Find common values in many tables

Question

I have 5 tables and I want to find the common values between them in one column. The column name differs in two tables(account_number, account,account_id).

select * from db.table1 as a 
INNER JOIN db.table2 as b
ON a.account = b.account_id
INNER JOIN db.table3 as c
ON a.account = c.account_number
INNER JOIN db.table4 as d 
ON a.account  = d.account_number
INNER JOIN db.table_5 as f
ON a.account = f.account_number;`

I tried the above with the idea of every time compare the last result of the inner join with the new one but it seems wrong.

Answer 1

Your method is okay, but it can return duplicates. Another method is to use union all:

select account
from ((select account, 'a' as which from db.table1) union all
      (select account_id, 'b' as which from db.table2) union all
      (select account_number, 'c' as which from db.table3) union all
      (select account_number, 'd' as which from db.table4) union all
      (select account_number, '3' as which from db.table5) 
     ) t
group by account
having count(distinct which) = 5;

Note that this can be easily tweaked to get accounts that are in three or four tables rather than in all of them.