Parent Child relationships

[Cabaal]

When designing a BBS an easy way, with Oracle is to use the "connect by" statement. Example:
select whatever
from table_name
start with id = #id_choice#
connect by prior id = parent_id
Does anyone know if the equivalent statement exists in SQL server? Basically you have each record associated with a parent record. So you can simply drill down through a tree and get all the children of a record. This easily creates a BBS type system.
Thanks in advance

 

[Bernadette]

Hi There

Try

select <columnname>, <column_name2> .....
from table1, table2
where table1.id = table2.id
and table1.id = <id_choice>

Hope This Helps
Bernadette

 

[MTydeman]

Hi Cabaal

I am trying to find an equivalent in SQL Server for the Oracle Connect By statement as I want to create a tree structure and be able to drill down through it exactly as you explained.

Did you manage to find an equivalent (other than writing some rather horrible nested subqueries) ?
Any advice you can give would be most appreciated.

Regards
Michelle