Link to home
Start Free TrialLog in
Avatar of tmajor99
tmajor99

asked on

SQL Select - Conditional Inner Join?

I am having a problem defining a SQL Select query (see below) to align a vertical set of rows horizontally.

Here is data in my table:

 ID          Name        Object Type   Parent ID
  1          Test              Level 1              
 2          Sample          Level 2         1
 3          .........              Level 3         2
 4.        ...........            Level 3          2
 5        ............            Level 2          1
 9        ............            Level 1        

In a treeview it looks like this:
- 1 (level 1)
    - 2 (level 2)
           - 3  (level 3)
           - 4  (level 3)
    - 5  (this level 2 does not have a level 3)
- 9
Not every Level 2 has a level 3 but I still want the row to be included.  

 I would like this data to be formatted like this:

 Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name
     1                  Test                            2          Sample                3               ...............
     1                  Test                            2          Sample                4              ...............
     1                  Test                            5          Sample           (no level 3)
     9                  ....                          

My problem with my SQL Select below is that it will not include rows if level2 does not have a level3.   Is it possible to have a conditional inner join?
select 
l1.[<ID>] as 'L1-ID',l1.[<Name>] as 'L1-Name',l1
l2.[<ID>] as 'L2-ID',l2.[<Name>] as 'L2-Name',
l3.[<ID>] as 'L3-ID',l3.[<Name>] as 'L3-Name',
from [STEP-DEV-L1-4]  l1
Inner join [STEP-DEV-L1-4] l2 on l2.[<Parent ID>]=l1.[<ID>]
Inner join [STEP-DEV-L1-4] l3 on l3.[<Parent ID>]=l2.[<ID>]    --->  "I need this to include to not exclude the record just because there is no level 3.

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Yuval_Shohat
Yuval_Shohat
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tmajor99
tmajor99

ASKER

Left joins returns too many rows.  Basically I am getting all rows from each of the joins above.  Maybe I just need a group by or something like that?

  Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name
       1                  Toys      
       9                  Electronics                  
       1                  Toys                           2          Bikes              
       1                  Toys                           2          Bikes              
       1                  Toys                           5          Dolls            
       1                  Toys                           2          Bikes              3               Pedal
       1                  Toys                           2          Bikes              4               Motor
You can add a DISTINCT right after your SELECT and see if the duplication is gone.
SELECT DISTINCT <the rest of your query>

Open in new window

tmajor99, can you post your query?

 -=Yuval=-