postgresql query to select rows in a table that don't exist in another table
I have two tables:
Table "contact"
id | customer_id
----+-------------
1 | 123
2 | 123
3 | 123
4 | 888
And
Table "user_contact"
user_id | contact_id
--------+------------
456 | 1
456 | 2
789 | 3
999 | 4
To select all of contacts that have a customer_id of 123 and exist in
user_contact with a user_id of 456, I can go:
SELECT
contact.id
FROM
contact JOIN user_contact ON
contact.id = user_contact.contact_id
WHERE
contact.customer_id = 123 AND
user_contact.user_id = 456
How can I select all of the contacts that have a customer_id of 123 but
don't exist in user_contact with a user_id of 456?
Trying:
SELECT
contact.id
FROM
contact JOIN user_contact ON
contact.id = user_contact.contact_id
WHERE
contact.customer_id = 123 AND
user_contact.user_id != 456
Obviously doesn't work as it returns a row for each contact in
user_contact that has a user_id != 456.
No comments:
Post a Comment