CODEWITHSUNDEEP
mysqlsql
4m1r
4m1r

Reputation: 12552

Identifying values that are not existent in other table

I've got two tables that have one to many associations on a pmid. So if one table has an pmid, the second table should have multiple rows with the same pmid. However, something went sideways and I'm missing my latest batch of pmids in the second table. These queries, should help illustrate the problem, but I can't figure out how to get the ids from the first table that are actually missing in the second table.

select count(*) from abstract_mesh am; #2167101

select count(*) from abstract_mesh am
join abstracts a on am.pmid = a.pmid; #2133848

select 2167101 - 2133848;  #33253

select count(*) from abstract_mesh where pmid is NULL; #33253

So as you can see there are 33,253 rows in abstract_mesh that have no pmids. I simply want to identify which pmids I should be interested in from the abstracts table.

Upvotes: 1

Views: 36

Answers (2)

onedaywhen
onedaywhen

Reputation: 57073

You need and anti-join. SQL lacks an explicit anti-join operator. Standard SQL has EXCEPT (relational minus) by mySQL lacks this. Here I'm using NOT IN <table expression> to simulate anti-join (though not 100% sure I have the tables the right way round):

  SELECT DISTINCT pmid
    FROM abstract_mesh 
   WHERE pmid NOT IN ( SELECT pmid FROM abstracts );

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30849

You can use NOT EXITS to filter out the records, e.g.

select * 
from table1 t1 
where not exists
select * from table2 t2 where t1.pmid = t2.pmid;

Upvotes: 1

Related Questions