[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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

0
johnkainn
Asked:
johnkainn
1 Solution
 
zadeveloperCommented:
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
?             ?          ?        ?           ?             ?              ?           ?
0
 
johnkainnAuthor Commented:
The extra row should be:
T1Id    T1Text  T2Id    T2Id    T2Text  T3Id    T3Text  T2ID
1         Un       NULL   NULL   NULL   NULL  NULL    NULL
0
 
waltersnowslinarnoldCommented:
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
0
 
zadeveloperCommented:
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

0
 
SharathData EngineerCommented:
try this

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 all

select distinct                    
        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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now