How to use an outer column in a subquery
I have the following request -
select * from
Table1 t1 , table2 t2 ,
(select idCol from table3) t3
My question is, can I use table t2 inside a subquery?
like this
select * from
Table1 t1 , table2 t2 ,
(select idCol, t2.nameCol from table3) t3
Obviously this gives an error invalid identifier t2.nameCol
But if I write like this, it provides extra lines
select * from
Table1 t1 , table2 t2 ,
(select idCol, t2.nameCol from table3, table2 t2) t3
any other way to do this?
EDIT
Basically what I am trying to achieve follows
select * from
Table1 t1 , table2 t2 ,
(select
case
when t2.nameCol = 'ABC' then 'ABC'
else idCol
end idCol from table3) t3
+3
source to share
1 answer
To join a table only when certain criteria are met is called an outer join. Here's how:
select *
from table1 t1
inner join table2 t2 on <join criteria here>
left outer join table3 t3 on t2.namecol <> 'ABC' and <join criteria here>;
However, in your case, it may be sufficient to move the subquery into the SELECT clause:
select
t1.*,
t2.*,
case when t2.namecol = 'ABC' then
'ABC'
else
(select idcol from table3 t3 where <join criteria here>)
end
from table1 t1
inner join table2 t2 on <join criteria here>;
0
source to share