asked on

Left outer join + extra row

In the code you can see a query and its result.
If T2ID is not NULL in Table1 I would like also to return extra row
1 Un NULL NULL NULL
So that when T2ID=1 I would return 4 rows instead of 3.
Is that possible?

I have three tables:
Table1
T1Id   T1Text  T2Id
Table2
T2Id T2Text
Table3
T3Id T2Id T3Text

select * from Table1 t1
Left outer join Table2 t2
on t2.T2Id=t1.T1Id
LEFT OUTER JOIN Table3 t3
on t3.T2Id=t2.T2Id

Results are:
T1Id    T1Text  T2Id    T2Id    T2Text  T3Id    T3Text  T2ID
1	Un	1	1	One	1	Eins	1
1	Un	1	1	One	2	Zwei	1
1	Un	1	1	One	3	Drei	1
2	Dos	NULL	NULL	NULL	NULL	NULL	NULL
3	Tres	NULL	NULL	NULL	NULL	NULL	NULL
4	Cuatro	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window

Jarrod

In your extra row (1 Un NULL NULL NULL ) - What fields do these corrospond to, and what should the rest of the fields be ?
IE: Extra row should read:
T1Id T1Text T2Id T2Id T2Text T3Id T3Text T2ID
? ? ? ? ? ? ? ?

johnkainn

ASKER

The extra row should be:
T1Id T1Text T2Id T2Id T2Text T3Id T3Text T2ID
1 Un NULL NULL NULL NULL NULL NULL

waltersnowslinarnold

Try the following code sample,

select * from Table1 t1
OUTER JOIN Table2 t2
on t2.T2Id=t1.T1Id
LEFT OUTER JOIN Table3 t3
on t3.T2Id=t2.T2Id

Jarrod

Try

select * from Table1 t1 
Left outer join Table2 t2 
on t2.T2Id=t1.T1Id 
LEFT OUTER JOIN Table3 t3 
on t3.T2Id=t2.T2Id 

union

select 	                   
	1 as T1Id, 
	'Un' as T1Text,
	null as T2Id,
	null as T2Id,
	null as T2Text,
	null as T3Id,
	null as T3Text,
	null as T2ID
from 
	Table1 t1 
where 
	t1.t2ID is not null

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial