T-SQL: How do I get the rows from one table whose values completely match up with values in another table?

sql-server, t-sql

Solution

Probably not the cheapest way to do it:

SELECT a.pkId,b.otherId FROM
    (SELECT a.pkId,CHECKSUM_AGG(DISTINCT a.value) as 'ValueHash' FROM @a a GROUP BY a.pkId) a
    INNER JOIN (SELECT b.otherId,CHECKSUM_AGG(DISTINCT b.value) as 'ValueHash' FROM @b b GROUP BY b.otherId) b
ON a.ValueHash = b.ValueHash

You can see, basically I'm creating a new result set for each representing one value for each Id's set of values in each table and joining only where they match.

Problem

Given the following: ``` declare @a table ( pkid int, value int ) declare @b table ( otherID int, value int ) insert into @a values (1, 1000) insert into @a values (1, 1001) insert into @a values (2, 1000) insert into @a values (2, 1001) insert into @a values (2, 1002) insert into @b values (-1, 1000) insert into @b values (-1, 1001) insert into @b values (-1, 1002) ``` How do I query for all the values in @a that completely match up with @b? `{@a.pkid = 1, @b.otherID = -1}` would not be returned (only 2 of 3 values match) `{@a.pkid = 2, @b.otherID = -1}` would be returned (3 of 3 values match) Refactoring tables can be an option. EDIT: I've had success with the answers from James and Tom H. When I add another case in @b, they fall a little short. ``` insert into @b values (-2, 1000) ``` Assuming this should return two additional rows (`{@a.pkid = 1, @b.otherID = -2}` and `{@a.pkid = 2, @b.otherID = -2}`, it doesn't work. However, for my project this is not an issue.

Original source