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.
tmajor99Asked:
Who is Participating?

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

x
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.

David ToddSenior Database AdministratorCommented:
Hi,

I added a where clause to your query.

What you are programming here is a heirachy, and often done better with a CTE ie https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx.

It may be better to add a level column to your table, which would make the where clause a little more readable/understandable.

HTH
  David

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.eComm' )
		and type in( N'U' )
	)
	drop table dbo.eComm
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.eComm' )
		and type in( N'U' )
	)
	create table dbo.eComm (
		ID varchar( 20 )
		, Name varchar( 20 )
		, ParentID varchar( 20 )

	) on [PRIMARY]
go

insert dbo.eComm values
( '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' )
;

--
select 
	l1.Name as 'L1-Name', 
	l2.Name as 'L2-Name',
	l3.Name as 'L3-Name'
from dbo.eComm  l1
left outer join dbo.[eComm] l2 on 
	l2.ParentID = l1.ID
left outer join dbo.[eComm] l3 
	on l3.ParentID = l2.ID
where
	l1.ParentID = 'webfolder2-1'
;

Open in new window

0
YZlatCommented:
try something like this:

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>] 
WHERE l1.[Parent ID]='Root'
and l2.[Parent ID] IN (SELECT ID FROM [eComm] WHERE [Parent ID]='Root')
and l3.[Parent ID] IN (SELECT ID FROM [eComm] WHERE [Parent ID] != 'Root')

Open in new window

0

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
tmajor99, do you still need help on this question?
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.