Using SQL to compare the number of identifiers from two tables

Question

I am trying to build a query that compares two tables with the same structure

Table 1: ID | Data Table 2: ID | Data 

An identifier is a non-living key (data is repeatable, but ID | Combined data is unique). I need a list of identifiers, where the COUNT of these identifiers is more 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 

will generate output "b"

It looks like it should be easy, but now my head is scrambled. I do this in mysql if this affects the parameters.

Answer 1

To get a counter for each key,

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

So, use this as a subquery 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 

Answer 2

Tested in MySQL.

 select case when count(d1.id) > count(d2.id) then d1.id end as 'id' from (select id from t1) d1, (select id from t2) d2 group by d1.id