SQL Server - Find row patterns group by a unique key

user_3946433 0
Viewed 33

I need a query that would compare two tables and find exact matching set of rows. I have two tables

First table

ID   uniqueKey

 1    A
 2    A
 3    A
 1    B
 2    B
 4    C

IF i pass another table with following data

ID
1
2
3

Then it should return 'A'

If the input table is 

ID
1
2

It should return 'B'

IF the input table is 

ID
1
2
3
4

It should not return anything

If the input table is

ID
 1

Then it should not return anything

sql-server sql
2 Answers

I'm not sure what you mean by "passing a table", but you can do what you want as:

select t1.uniqueKey  -- doesn't seem very unique to me
from table1 t1 left join
     table2 t2
     on t1.id = t2.id
group by t1.uniqueKey
having count(*) = count(t2.id) and -- all ids in table1 match table2
       count(*) = (select count(*) from table2)  -- all table2 ids are present
user_11440350

Alternatively, array_agg can also be used,

select V2 as u_key from
      (select array_agg(ID)as V1,unique_key as V2 from t1 group by unique_key) Table1 
      join 
      (select array_agg(ID) as V3 from t2) Table2 
      on Table1.V1= Table2.V3;
user_46583120
Related