Search code examples
postgresqljoinnot-exists

“Not exists” across multiple joins

I am learning sql (postgres) and id like to find values that do not exist.

I have a table, table 1 with ids and i want to find those ids that are not in table 4.

I have to join between 3 tables as table 1 holds id and table 4 contact_id (not the same number)

The tables 2,3 need to be joined as that connects the ids.

So how do i do that with “not exists”?

Select t1.id, table4.contact_id
From table1 t1
Join table2 using(id)
Join table3 using(id)
Join table4 using(contact_id)
Where not exists (
  Select 1
  From table4
  Where table4.contact_id=t1.id
  );

It returns no values, but should No error msg…

I have thinking error i assume

Solution

  • Your query probably returns no values because you join table4 on contact_id and then you exclude in the WHERE clause the rows which come from this join.

    To find values that don't exist, you can usually use LEFT JOIN or RIGHT JOIN or FULL OUTER JOIN and then filter the rows with NULL values in the WHERE clause.

    Try this :

    SELECT t1.id
FROM table1 t1
LEFT JOIN table2 t2 using(id)
LEFT JOIN table3 t3 using(id)
LEFT JOIN table4 t4 using(contact_id)
WHERE t4.contact_id IS NULL