Hello,
Here's a puzzle for all you 'Joe Celko' types:
Am trying to figure out how to do on SQL Server something I can do in
Oracle. I think this problem is variously known as Subtypes-Supertypes,
Circular Reference, Entity Tree, etc...
Given this data:
Columns: iEntityID, iSubEntityOf, sName
1 1 Mary
2 2 1 Fritz
3 3 1 John
4 4 2 Abu
5 5 2 Ludwig
6 6 3 Abigail
7 7 3 Josef
8 8 6 Mark
9 9 6 Ben
10 10 6 Habib
11 11 9 Paul
12 12 11 Mahatma
I want to graphically represent the tree in this data - i.e. Mary is the
boss; Fritz and John report to Mary; Abu and Ludwig report to Fritz;
etc...
The important thing is that the SubEntityOf column is kind of a foreign
key to the primary key EntityID, such that the tree can be infinitely
deep.
I used to be able to do this in Oracle using:
<CFQUERY NAME="GetEntities" DATASOURCE="MyDatasource">
SELECT iEntityID, iSubEntityOf, sName
FROM Mytable
WHERE NOT iEntityID
START WITH iEntityID=1
CONNECT BY PRIOR iEntityID=iSubEntityOf
</CFQUERY>
But I can't use these functions in SQL Server...
Thanks!
-Jason
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
