SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connect By Prior


Connect By Prior

Author
Message
Tarek Soukieh
Tarek Soukieh
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
Hi everyone,

I was wondering if anyone knows of a function that can do something similar to Oracle 'Connect By Prior'. Or does anyone knows how to connect hirarchies in SQL Server. Thanks,



Sean Burke
Sean Burke
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1
I wasn't familiar with this operator in Oracle (and many others, for that matter). Here's what I found on thier site:

SELECT RPAD( ' ', LEVEL * 5 ) || Name FROM Universe 
CONNECT BY PRIOR Name = Parent START WITH Parent IS NULL;

World
Europe
England
Germany
The Netherlands
Asia
Japan
China
America
United States
Mexico
Africa
Egypt
Morocco


I am not aware of any intrinsic SQL Server fucntionality to handle hierarchical data in this fashion; it would normally be done through a series of joins.

When you execute this sql in Oracle, how would you go about walking the tree?



Tarek Soukieh
Tarek Soukieh
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
Hi Sean,
Thank you for your fast reply. Do you have an example of how to connec hirarchies in SQL Server.
Also I did not understand your question at the end!



Sean Burke
Sean Burke
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1
Just to exend the example I copied from the Oracle site, if you have a table called "Universe" and two columns 'Parent" and "child", a similar result can be obtained by a simple join like this
Select A.Parent, A.Child, B.child from universe A 
Left Join universe b on
a.child = b.parent
where b.child is not null


Not the most stellar example, but it will yeild these results
World   Europe   Germany
World Europe France
World America Canada
World America Mexico
World Africa Egypt
World Africa Nigeria
World Asia Japan
World Asia China


Here you have a flat representation of your hierarchical data. It is easy enough to query against, or for whatever other reason you may need the data in this representation. My question was regarding the output from the Oracle query; in what fashion would you use these results? As a report? With a little extra effort, SQL Server can achive the same look and feel if needed. However, there are other companion technologies (like ADO) that are useful to achieve the same end.

Sean



ramgbile
ramgbile
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 0
WITH n(id, name) AS
(SELECT id, name
FROM tulieu
WHERE parent = 44
UNION ALL
SELECT m.id, m.name
FROM tulieu as m, n
WHERE n.id = m.parent)
SELECT * FROM n
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search