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?
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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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

tmajor99, can you post your query?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.