Link to home
Start Free TrialLog in
Avatar of tmajor99
tmajor99

asked on

SQL Server - Help with Left Join

I need help with a SQL Server Left Join.  

<ID>      <Name>      <Parent ID>
webfolder2-1               Appliances              Root
webfolder2-257      Appliance Parts      webfolder2-1
webfolder2-1121      Dishwasher Parts      webfolder2-257
webfolder2-1122      Disposer Parts      webfolder2-257
webfolder2-258      Kitchen                      webfolder2-1
webfolder2-1117      Cooking                      webfolder2-258
webfolder2-261      Small Appliances      webfolder2-1

In a TREEVIEW the data looks like this:
Root
   -    Appliance
        - Appliance Parts
           - Dishwasher Parts
           - Disposer Parts
        - Kitchen
        - Small Appliances

I am trying to develop a SQL Select that will display the data that looks like below.  As you can see this is a staggered hierarchy.
Level 1 Name      Level 2 Name      Level 3 Name
Appliances             Appliance Parts      Dishwasher Parts
Appliances             Appliance Parts      Disposer Parts
Appliances             Kitchen       
Appliances             Small Appliances      

My SQL Select design below is getting me some strange results as follows:
Level 1 Name      Level 2 Name      Level 3 Name
Appliances              Appliance Parts             Dishwasher Parts
Appliances             Appliance Parts             Disposer Parts
Appliances             Kitchen                             Cooking
Appliances             Small Appliances      
Appliance Parts      Dishwasher Parts      
Appliance Parts      Disposer Parts      
Dishwasher Parts            
Disposer Parts            
Kitchen                       Cooking      
Cooking            
Small Appliances            

Here is my SQL Select:
select
    l1.[<Name>] as 'L1-Name',
    l2.[<Name>] as 'L2-Name',
    l3.[<Name>] as 'L3-Name'
from [eComm]  l1
   left join [eComm] l2 on l2.[<Parent ID>]=l1.[<ID>]
  left join [eComm] l3 on l3.[<Parent ID>]=l2.[<ID>]

Please help me.
SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand 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
ASKER CERTIFIED SOLUTION
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
tmajor99, do you still need help on this question?