Nested join

ID IAParent Entry Level
110 95 Request [NULL] 4
111 95 Install [NULL] 4
112 95 Remove [NULL] 4
113 76 Power [NULL] 5
114 109 Power [NULL] 5
115 109 Display [NULL] 5
116 109 Keyboard/Touchpad [NULL] 5
117 109 Docking Station [NULL] 5
118 109 Memory [NULL] 5
119 109 Reimage Machine [NULL] 5
120 109 CD/Floppy Drive [NULL] 5
121 109 General Diagnostics [NULL] 5
122 109 Modem [NULL] 5
123 109 Setup/Configuration [NULL] 5
124 109 Vendor Repair [NULL] 5
125 109 Virus [NULL] 5
126 76 Miscellaneous [NULL] 5
127 109 Miscellaneous [NULL] 5
128 112 User Leaving Firm [NULL] 5
129 110 Loaner [NULL] 5
Hello all,
I have a question about self join. From the table info above, there are
five levels that are in the same table.
For example...ID 129 has a parent of 110... then 110 has another parent
of 95 and so on till you get to level 1..
how can I write a join query that will show me level 1 through 5?
Thanks!Level is redundant in your table because it can obviously be derived by
counting the levels above each node. For that reason it would probably be
wise to drop the Level column. Here's one solution:
SELECT T.id, T.iaparent, T.entry,
SIGN(ISNULL(id1,0))+SIGN(ISNULL(id2,0))+
SIGN(ISNULL(id3,0))+SIGN(ISNULL(id4,0))+
SIGN(ISNULL(id5,0))-
CASE T.id
WHEN id1 THEN 0
WHEN id2 THEN 1
WHEN id3 THEN 2
WHEN id4 THEN 3
WHEN id5 THEN 4
END AS level
FROM your_table AS T,
(SELECT T1.id, T2.id, T3.id, T4.id, T5.id
FROM your_table AS T1
LEFT JOIN your_table AS T2
ON T1.iaparent = T2.id
LEFT JOIN your_table AS T3
ON T2.iaparent = T3.id
LEFT JOIN your_table AS T4
ON T3.iaparent = T4.id
LEFT JOIN your_table AS T5
ON T4.iaparent = T5.id
WHERE T1.id = @.id) AS L(id1,id2,id3,id4,id5)
WHERE id IN (id1,id2,id3,id4,id5)
ORDER BY level ;
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You also need to get a copy of TREES & HIERARCHIES IN SQL or to Google
"Nested Set Model" for trees.|||Levels can be computed and you can do this in one simple self-joined
query for any level.