Reject row from select if condition exists in another table
I have two tables.
- Contacts table - contact_id, contact_email, contact_name, etc
- Opt out table - contact_email, scope of their opt out (event, company)
So one contact maybe have multiple event opt-outs but what I really care about is if they have a company wide opt out.
How do I join the data so that if a contact has a match with any row in the optout table that has a scope of "company" it will not show up in the result?
SELECT c.*
FROM Contacts c
WHERE NOT EXISTS(SELECT NULL
FROM OptOut o
WHERE c.contact_email = o.contact_email
AND o.scope = 'company')
This could also be done with a LEFT JOIN
:
SELECT c.*
FROM Contacts c
LEFT JOIN OptOut o
ON c.contact_email = o.contact_email
AND o.scope = 'company'
WHERE o.contact_email IS NULL
select * from contacts where contact_email not in (select contact_email from optout where scope = 'company');
精彩评论