Remove select subquery from column list to main query

1090 views mysql
0

Query 1 (Before):

select ta.C1, (SELECT tb.C1 from T2 tb WHERE  tb.C2 = ta.C2)
from T1 ta WHERE ta.C3=30025239;

Result Query 1

I want to remove the subquery from column level. I modified the code to add join

Query 2 (After):

select ta.C1, tb.C1
from T1 ta left outer join T2 tb on tb.C2 = ta.C2
WHERE ta.C3=30025239;

Result Query 2

But if subquery returns blank (no value) then Query 1 returns data for ta.C1 and null for tb.C1 whereas Query 2 will return blank (no result). I want result of Query 2 as same as Query 1

answered question

1 Answer

10

Use your others conditions in ON Cluase instead of Where Clause as when you use where cluase it left/right outer join it workes as inner join

select ta.C1, tb.C1
from T1 ta left outer join T2 tb on tb.C2 = ta.C2
and ta.C3=30025239;

posted this

Have an answer?

JD

Please login first before posting an answer.