CODEWITHSUNDEEP
sqlmysql
itsmatt
itsmatt

Reputation: 31406

SQL query to find rows that aren't present in other tables

Here's what I'm trying to accomplish:

I've got two tables, call them first and second. They each have an ID column. They might have other columns but those aren't important. I have a third table, call it third. It contains two columns, ID and OTHERID. OTHERID references entries that may or may not exist in tables first and second.

I want to query third and look for rows who don't have an OTHERID column value that is found in either tables first or second. The goal is to delete those rows from table third.

Example:

first table:

ID
1
2
3

second table:

ID
6
7
8

third table

ID  | OTHERID
21        1
22        2
23        3
24        4
25        5
26        6
27        7
28        8

In this case, I'd want to retrieve the IDs from third who don't have a matching ID in either table first or table second. I'd expect to get back the following IDs:

24
25

What I've tried:

I've done something this to get back the entries in third that aren't in first:

select t.* from third t where not exists (select * from first f where t.otherid = f.id);

and this will get me back the following rows:

ID  | OTHERID
24        4
25        5
26        6
27        7
28        8

Similarly, I can get the ones that aren't in second:

select t.* from third t where not exists (select * from second s where t.otherid = s.id);

and I'll get:

ID  | OTHERID
21        1
22        2
23        3
24        4
25        5

What I can't get my brain about this morning is how to combine the two queries together to get the intersection between the two results sets, so that just the rows with IDs 24 and 25 are returned. Those would be two rows I could remove since they are orphans.

How would you solve this? I think I'm on the right track but I'm just spinning at this point making no progress.

Upvotes: 6

Views: 5532

Answers (3)

Serty Oan
Serty Oan

Reputation: 1726

Maybe this :

SELECT third.*
FROM third
LEFT JOIN first ON third.otherID = first.id
LEFT JOIN second ON third.otherID = second.id
WHERE first.id IS NULL AND second.id IS NULL

Upvotes: 15

Aaron Digulla
Aaron Digulla

Reputation: 328556

Just use

select t.*
from third t
where 
       not exists (select * from first f  where t.otherid = f.id)
  and  not exists (select * from second s where t.otherid = s.id)

Upvotes: 8

SAGExSDX
SAGExSDX

Reputation: 703

SELECT t.ID
FROM third t
WHERE t.OTHERID NOT IN (
    SELECT ID
    FROM first

    UNION

    SELECT ID
    FROM second
)

Upvotes: 4

Related Questions