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

tmajor99Asked:
Who is Participating?
 
Yuval_ShohatCommented:
well,
Haven't tested it but from your description it looks like you need to use left join instead of inner join.
inner join excludes the row of level 2 since it has nothing to join it in the next inner join of the query.
left join will match the l2 table to the results of l1 and another left join will do about the same and match the l3 results to those of l2 regardless if there is match in l3 or not.

IMO, as regards to hierarchy and trees, the approach of left and right thought much complicated to understand is something worth knowing and understanding.
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

-=Yuval=-
0
 
tmajor99Author Commented:
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
0
 
Walter RitzelSenior Software EngineerCommented:
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

0
 
Yuval_ShohatCommented:
tmajor99, can you post your query?

 -=Yuval=-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.