Query Help

[SriRam]

Table :

ID NAME HEAD
1 A
2 B 1
3 C 2
4 D 3

where HEAD referred from ID

What is the Query to output as follows

Name Head
A
B A
C B
D C

 

[Tom Ellison]

Dear Sri:

When a table contains a column that references another column in that same
table, it is called a self-reference. That can be accomplished in a query
with a JOIN to the same table. As your "linking" column HEAD can be NULL,
indicating no reference (as it the top of a hierarchy) it will need to be a
LEFT JOIN (or a RIGHT JOIN, but I generally avoid those, prefering to start
at the top and work down.)

SELECT T1.[Name], T2.[Name] AS Head
FROM YourTable T1
LEFT JOIN YourTable T2
ON T2.ID = T.Head

A complete hierarchy can be produced as well:

A
A B
A B C
A B C D

Don't know if that would ever become interesting for you.

Tom Ellison

 

[SriRam]

Hi Tom,

Thanks for the help.


Sri Ram