CODEWITHSUNDEEP
mysqlsql
Leander Leo Lagardo
Leander Leo Lagardo

Reputation: 91

How to perform LEFT JOIN with condition

I want to perform something like this

SELECT  g.Name, g.emailID, g.phoneNo
FROM roomAllocation as ra
// if ra.guest_email is NOT NULL
LEFT JOIN Guests as g ON ra.guest_email = g.emailid
// else
LEFT JOIN Guests as g ON ra.guestPhoneNo = g.PhoneNo

Upvotes: 0

Views: 1271

Answers (4)

Svein Terje Gaup
Svein Terje Gaup

Reputation: 1578

Guess you could split it up into 2 queries with a UNION:

SELECT  g.Name, g.emailID, g.phoneNo   
FROM roomAllocation as ra 
LEFT JOIN Guests as g ON ra.guest_email = g.email_id  
UNION  
SELECT  g.Name, g.emailID, g.phoneNo   
FROM roomAllocation as ra 
LEFT JOIN Guests as g ON ra.guest_phoneNo = g.phoneNo

Using UNION will merge the two results if they are overlapping.

Upvotes: 1

Somy
Somy

Reputation: 1624

I believe this would work

 LEFT JOIN Guests as g
 ON (case when RA.guest_email IS NOT NULL 
 then RA.guest_email else RA.guest_phoneNo end) = (case when RA.guest_email IS NOT NULL then g.email_id else g.phoneNo end)

Upvotes: 1

Akina
Akina

Reputation: 42611

Formally

LEFT JOIN Guests as g
     ON CASE WHEN table1.guest_email NOT NULL
             THEN table1.guest_email = table2.email_id
             ELSE table1.guest_phoneNo = table2.phoneNo
             END

But this kills indices usage. I'd recommend Gordon Linoff's solution.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand correctly, you want to conditionally join to a table. I would recommend two left joins as in:

select t1.*,
       coalesce(ge.name, gp.name) as name
from table1 t1 left join
     table2 ge
     on t1.guest_email = ge.email_id left join
     table2 gp
     on t1.guest_phoneNo = gp.phoneNo and t1.guest_email is null;

Because it is the same table, you could also use or in the on clause:

select t1.*, g.*
from table1 t1 left join
     table2 g
     on t1.guest_email = geemail_id or
        (t1.guest_phoneNo = g.phoneNo and t1.guest_email is null);

However, or in an on clause usually kills performance.

Upvotes: 4

Related Questions