Display Tree structure Data

[tarriqSQL]

hi ,
I have a Table named TreeDisplay in my database

The REcords are like
NodeID NodeName NOdeREfID
1 Fixed Assets 0
22 Buildings 1
25 Fixed Assets - temp 1
23 Office Buildings 22
24 Factory Buildings 22
26 FIXED TEMP- 1 25
27 FIXED TEMP- 2 25
30 FIXED TEMP- 1 - 1 26

i need to Display all the descendents of Node Id 1
ie

1
22
23
24

25
26
30
27

[cvandemaele]

If you are using Oracle, check out the CONNECT BY clause in the select statement to "solve" hierarchical queries.

Here is an example I copied from the Oracle documentation.

>>>>>
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
.
.
.


The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
...


Finally, the next example adds a START WITH clause to specify a root row for the hierarchy, and an ORDER BY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL
------------------------- ----------- ---------- ----------
King 100 1
Cambrault 148 100 2
Bates 172 148 3
Bloom 169 148 3
Fox 170 148 3
Kumar 173 148 3
Ozer 168 148 3
Smith 171 148 3
De Haan 102 100 2
Hunold 103 102 3
Austin 105 103 4
Ernst 104 103 4
Lorentz 107 103 4
Pataballa 106 103 4
Errazuriz 147 100 2
Ande 166 147 3
Banda 167 147 3
...
<<<<<

[tarriqSQL]

Thanks cvandemaele

Thank u for the reply
but im using SQL server 7.0/2k

Sorry for not specifying it
Please help me if u could find one
Thanks in ad