Connect By Prior

Question

Connect By Prior

Tarek Soukieh

SSC-Addicted

Points: 490
More actions
August 28, 2001 at 2:21 pm

#157509

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,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Burke SSCommitted Points: 1504 More actions · Answer 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 SSC-Addicted Points: 490 More actions · Answer 2

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 SSCommitted Points: 1504 More actions · Answer 3

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

WorldEuropeGermany

WorldEuropeFrance

WorldAmericaCanada

WorldAmericaMexico

WorldAfricaEgypt

WorldAfricaNigeria

WorldAsiaJapan

WorldAsiaChina

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 Valued Member Points: 61 More actions · Answer 4

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