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

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

# 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
``````
0
johnkainn
1 Solution

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

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

Commented:
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

Commented:
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
``````
0

Data 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

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