CODEWITHSUNDEEP
mysqlsqlleft-joincase
lanyou
lanyou

Reputation: 3

How to apply CASE in left join table

I have one query for MySQL, and need to left join twice. There are four tables A, B, C, D, and two possibilities, if A.label is null then, 

select ... from A
left join B on B.name=A.name
left join D on B.bid=D.id;

otherwise, if A.label is not null, I have 

select ... from A
left join C on C.name=A.name
left join D on C.cid=D.id;

If I want to merge these two queries into one via CASE condition, what should I do?

Upvotes: 0

Views: 1897

Answers (3)

Barmar
Barmar

Reputation: 782166

Use a UNION:

select ... from A
left join B on B.name=A.name
left join D on B.bid=D.id;
where A.label IS NULL
UNION ALL
select ... from A
left join C on C.name=A.name
left join D on C.cid=D.id
WHERE A.label IS NOT NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Assuming that your queries don't product duplicate rows, you can do both joins in one query. Then determine which value to use by incorporating a case statement in the select clause:

select A.*,
       (case when a.label is null then b.value else c.value end) as value,
       . . . 
from A left join
     B
     on B.name = A.name left join
     C
     on C.name = A.name left join
     D
     on C.cid = D.id;

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

Not entirely sure I follow what you're after, but you can add to your JOIN criteria and then use COALESCE():

select COALESCE(b.name,c.name) Name
    ....
from A
left join B 
   on B.name=A.name
   AND A.label IS NULL
left join D 
   on B.bid=D.id
left join C 
   on C.name=A.name
   AND A.label IS NOT NULL
left join E
   on C.cid=E.id

Upvotes: 1

Related Questions