Combining values from multiple columns into one column

Question

I have a hierarchy structure consisting of A,B,C,D. Each entity has a one-to-many relationship to its child (A -> 1:N -> B -> 1:N -> C -> 1:N -> D).
I have another entity, lets call it X, which can be linked to one of those hierarchical entities using LevelID and LevelTypeID fields (LevelID is a foreign key to A,B,C or D and LevelTypeID is a foreign key to a look up table which contains the names of A,B,C and D)

Based on supplied LevelID and LevelTypeID, I need a query which can give me the current hierarchical entity and any higher parent level entities.

The following is some sample data in the 4 tables

   A
         ID       
         --
         1       
   B
         ID       A_ID
         ----     -----     
         10       1
   C
         ID       B_ID
         ----     -----
         100      10
   D
         ID       C_ID
         -----    -----
         1000     100
         1001     100

Using LevelId = 100, LevelTypeId = 3 (C), query should return:

   LevelTypeId    LevelId
   -----------    -------
   3               100
   2               10
   1               1

Using LevelId = 1000, LevelTypeId = 4 (D), query should return:

   LevelTypeId    LevelId
   -----------    -------
   4               1000
   3               100
   2               10
   1               1

I was using query below, but I am not getting the correct results, especially when I filter on LevelId = 100, LevelTypeId = 3

select LevelTypeId, LevelId
from
   (    select  
            a.ID as [1], 
            b.ID as [2],
            c.ID as [3], 
            d.ID as [4]
        from A a
        left join B b on a.ID = b.A_ID
        left join C c on b.ID = c.B_ID
        left join D d on c.ID = d.C_ID
        where 
            (@levelTypeId = 4 and D.ID = @levelId)
            or
            (@levelTypeId = 3 and C.ID = @levelId)
            or
            (@levelTypeId = 2 and B.ID = @levelId)
            or
            (@levelTypeId = 1 and A.ID = @levelId)

   ) p   
unpivot
   (LevelId FOR LevelTypeId IN ([1], [2], [3], [4])) AS unpvt

Can anyone help me with this query?

Answer 1

Your existing query is returning all levels in the join, because the only level selection is occurring in the where clause - if any level matches the criteria, then all levels are selected. Instead, try:

select LevelTypeId, LevelId
from
   (    select  
            a.ID as [1], 
            b.ID as [2],
            c.ID as [3], 
            d.ID as [4]
        from A a
        left join B b on a.ID = b.A_ID and @levelTypeId >= 2
        left join C c on b.ID = c.B_ID and @levelTypeId >= 3
        left join D d on c.ID = d.C_ID and @levelTypeId >= 4
        where 
            (@levelTypeId = 4 and D.ID = @levelId)
            or
            (@levelTypeId = 3 and C.ID = @levelId)
            or
            (@levelTypeId = 2 and B.ID = @levelId)
            or
            (@levelTypeId = 1 and A.ID = @levelId)

   ) p   
unpivot
   (LevelId FOR LevelTypeId IN ([1], [2], [3], [4])) AS unpvt

SQLFiddle here.