tmajor99
asked on
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can add a DISTINCT right after your SELECT and see if the duplication is gone.
SELECT DISTINCT <the rest of your query>
tmajor99, can you post your query?
-=Yuval=-
-=Yuval=-
ASKER
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