Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Connect By Prior Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2001 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 28, 2001 12:00 AM
Points: 2, 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,




Post #896
Posted Tuesday, August 28, 2001 2:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 29, 2002 12:00 AM
Points: 72, 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?




Post #21073
Posted Tuesday, August 28, 2001 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 28, 2001 12:00 AM
Points: 2, 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!




Post #21074
Posted Tuesday, August 28, 2001 7:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 29, 2002 12:00 AM
Points: 72, 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




Post #21075
Posted Wednesday, April 24, 2013 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 1, 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
Post #1446099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse