CODEWITHSUNDEEP
mysqlsql
Gaurav Kumar Singh
Gaurav Kumar Singh

Reputation: 644

Convert NOT IN to LEFT JOIN

I have this query:

SELECT id from patients_member where id NOT IN
(SELECT msc.member_id 
    FROM journeys_memberstagechallenge msc
    WHERE msc.challenge_id = '2ab9a76c1ad211e7a1350242ac110003'
          AND msc.completed_date IS NOT NULL);

I need to update this query so that it only has left joins and conditions, so that my code generator can generate it automatically. Please help.

Upvotes: 0

Views: 103

Answers (2)

PSK
PSK

Reputation: 17943

You can try like following.

SELECT t1.id 
FROM   patients_member t1 
       LEFT JOIN journeys_memberstagechallenge t2 
              ON t2.member_id = t1.id 
                 AND t2.challenge_id = '2ab9a76c1ad211e7a1350242ac110003' 
                 AND t2.completed_date IS NOT NULL 
WHERE  t2.member_id IS NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This would look like:

select pm.id
from patients_member pm left join
     journeys_memberstagechallenge msc
     ON msc.member_id = pm.id and
        msc.challenge_id = '2ab9a76c1ad211e7a1350242ac110003' and
        msc.completed_date is not null
where msc.member_id is null;

The conditions on the second table go in the on clause. The where clause -- checking for no match -- implements the not in condition.

Upvotes: 0

Related Questions