Using SQL to compare counts of identifiers from two tables

mysql sql subquery correlated-subquery
Kyle Banerjee picture Kyle Banerjee · Feb 28, 2013 · Viewed 24.1k times · Source

I'm trying to construct a query that compares two tables with identical structure

Table 1:
ID | Data 

Table 2:
ID | Data

ID is a nonunique key (Data is repeatable but ID|Data combos are unique). I need a list of IDs where the COUNT of those IDs is greater in Table 2 than in Table 1.

So for example, if 

Table 1
a | data
a | data1    
b | data2

Table 2
a | data
a | data1
b | data2
b | data3

would generate the output "b"

This feels like it should be easy, but my head is scrambled right now. I'm doing this in mysql if that affects options.

Answer

Marlin Pierce picture Marlin Pierce · Feb 28, 2013

To get the count for each key,

select count(*) as count, ID from Table1 group by ID

So, use this as a sub-query in the from clause, and join the tables.

select tt1.ID
from (select count(*) as count, ID from Table1 group by ID) tt1
  inner join (select count(*) as count, ID from Table2 group by ID) tt2
     on tt1.ID = tt2.ID
where tt1.count < tt2.count

Related questions

Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
How to delete from select in MySQL?

This code doesn't work for MySQL 5.0, how to re-write it to make it work DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )) I want to delete columns that dont have unique id. …

Read More
MySQL DELETE FROM with subquery as condition

I am trying to do a query like this: DELETE FROM term_hierarchy AS th WHERE th.parent = 1015 AND th.tid IN ( SELECT DISTINCT(th1.tid) FROM term_hierarchy AS th1 INNER JOIN term_hierarchy AS th2 ON (th1.tid = …

Read More