subquery 1

[bgryfakis]

I need to join the table I'm using to itself multiple times.
ID_B joins to ID_A and there are up to 6 depths total.

ID_A DESCR ID_B DEPTH
1 CAR 0 1
2 PLANE 0 1
3 TRAIN 0 1
5 COMPACT 1 2
7 SUBCOMPACT 5 3
8 SUV 1 2
9 FIGHTER JET 2 2
10 COMMERCIAL 2 2
11 747 10 3
12 727 10 3


Desired output will have:
DEPTH1 DEPTH 2 DEPTH 3
CAR COMPACT SUBCOMPACT
CAR SUV
TRAIN
PLANE FIGHTER JET
PLANE COMMERCIAL 747
PLANE COMMERCIAL 727


Any help is appreciated.

 

[rac2]

Maybe-

SELECT a.DESCR, b.DESCR, c.DESCR
FROM MyTable a
LEFT JOIN MyTable b ON b.ID_B = a.ID_A
               AND b.DEPTH = 2
LEFT JOIN MyTable c ON c.ID_B = b.ID_A
               AND c.DEPTH = 3
WHERE a.DEPTH = 1

and use ISNULL() to display blanks when there are no lower level items.