Search code examples
sqlsql-serveransi-sql

Get groups that are exactly equal to a table

I have a query that groups easily. I need to get the groups that have exactly the same records to another table (relationship).

I'm using ANSI-SQL under SQL Server, but I accept an answer of any implementation.

For example:

Table1:

Id | Value
---+------
1  |  1
1  |  2
1  |  3
2  |  4
3  |  2
4  |  3

Table2:

Value | ...
------+------
  1   | ...
  2   | ...
  3   | ...

In my example, the result is:

Id |
---+
1  |

How imagined that it could be the code:

SELECT Table1.Id
FROM Table1
GROUP BY Table1.Id
HAVING ...? -- The group that has exactly the same elements of Table2

Thanks in advance!

Solution

  • You can try the following:

    select t1.Id
from Table2 t2
join Table1 t1 on t1.value = t2.value
group by t1.Id
having count(distinct t1.value) = (select count(*) from Table2)

    SQLFiddle