CODEWITHSUNDEEP
sqlpostgresql
Chris McKinnel
Chris McKinnel

Reputation: 15082

postgresql query to select rows in a table that don't exist in a link to 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.

Upvotes: 0

Views: 1997

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

This is generally better tackled with a NOT EXISTS correlated subquery, as performance is not so dependent on the number of child records per parent.

SELECT c.* 
FROM   contact c
where  c.customer_id = '123' and
       not exists (
         select null
         from   user_contact uc
         where  c.id       = uc.contact_id and
                uc.user_id = '456')

This will use a semi-join, which stops after finding a single child record.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

You can use a simple LEFT JOIN and check that the resulting user_contact part does not exist (ie. IS NULL);

SELECT c.* 
FROM contact c
LEFT JOIN user_contact uc ON c.id = uc.contact_id AND uc.user_id='456'
WHERE c.customer_id = '123' AND uc.contact_id IS NULL

An SQLfiddle to test with.

Upvotes: 4

Related Questions