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.
<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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
tmajor99, do you still need help on this question?