CODEWITHSUNDEEP
sqlsqlite
Thomas Tempelmann
Thomas Tempelmann

Reputation: 12079

Select records whose id does not appear in multiple tables

I have 3 tables. Two tables (par1, par1) both refer (child_id) to an id in the 3rd table (child).

I like to find orphans in the 3rd table, i.e. records that are not referenced by either of the other two tables.

If I had only once referencing table, I could write:

SELECT * FROM child WHERE id NOT IN (SELECT child_id FROM par1)

But how do I solve this for two referencing tables?

I'm using sqlite.

Upvotes: 0

Views: 2611

Answers (2)

CubicleSoft
CubicleSoft

Reputation: 2512

An alternative is to use LEFT OUTER JOIN:

SELECT child.*
FROM child LEFT OUTER JOIN par1 ON (child.id = par1.child_id)
           LEFT OUTER JOIN par2 ON (child.id = par2.child_id)
WHERE par1.child_id IS NULL AND par2.child_id IS NULL

Executing subqueries may or may not find the desired optimization paths in the SQLite index decision engine.

Upvotes: 1

lightbricko
lightbricko

Reputation: 2709

SELECT * 
FROM child 
WHERE id NOT IN (SELECT child_id FROM par1) AND 
      id NOT IN (SELECT child_id FROM par2)

Upvotes: 5

Related Questions