Convert NOT IN to LEFT JOIN

67
February 12, 2019, at 07:30 AM

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.

Answer 1

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 
Answer 2

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.

Answer 3

You should get the needed result with this query:

SELECT id FROM patients_member
LEFT JOIN journeys_memberstagechallenge msc ON mcs.member_id = patients_member.id
WHERE msc.challenge_id NOT IN ('2ab9a76c1ad211e7a1350242ac110003')
AND msc.completed_date IS NOT NULL;
READ ALSO
How take record with last date in MySQL?

How take record with last date in MySQL?

In MySQL database I have table with such structure:

56
Insert multiple Scrapy data into mysql

Insert multiple Scrapy data into mysql

I have done to scrapy one of website then it also able to adapt to multiple pageBut when I want to insert into database, there is difficulty that I dont know

34
Adding a mysql datasource to jboss AS 7

Adding a mysql datasource to jboss AS 7

I am trying to add a MySQL data source to JBoss AS 71

41
MySQL Trigger - Get the difference between old and new value of a json field

MySQL Trigger - Get the difference between old and new value of a json field

I have a table with a JSON field containing datas like this :

62