Jobi Jobi - 6 months ago 18
SQL Question

Join query with not equal

I want to get all unpaid male customers those who are not in any plan

SELECT cr.id, cr.type FROM mydb.customer cr
JOIN mydb.plan1 p1 on cr.id != p1.id
JOIN mydb.plan2 p2 on cr.id != p2.id
JOIN mydb.plan3 p3 on cr.id != p3.id
WHERE cr.type = 'male'


is this query correct?

Answer

You could use a series of three left joins along with IS NULL:

SELECT cr.id, cr.type
FROM mydb.customer cr 
LEFT JOIN mydb.plan1 p1
    ON cr.id = p1.id 
LEFT JOIN mydb.plan2 p2
    ON cr.id = p2.id
LEFT JOIN mydb.plan3 p3
    ON cr.id = p3.id
WHERE p1.id IS NULL AND p2.id IS NULL AND p3.id iS NULL AND
    cr.type = 'male'
Comments