How can I determine if a set of rows in one table (with a specific id) represents all of the rows in another table with the same id?

Question

How can I determine if a set of rows in one table (with a specific id) represents all of the rows in another table with the same id?

I don't know how to create a query to determine whether or not I have a success case, as noted below. I am trying to avoid using a WHILE loop in TSQL, and would much rather use a set-based operation.

Fail case:

Only 2 of the records in Table2 are represented in Table1.

Table1

Table1_id	Table2_id	special_id
1	11	47
2	11	48

Table2

Table2_id	special_id
11	45
11	46
11	47
11	48
11	49

Success case:

All records in Table2 are represented in Table1.

Table1

Table1_id	Table2_id	special_id
1	11	45
2	11	46
3	11	47
4	11	48
5	11	49

Table2

Table2_id	special_id
11	45
11	46
11	47
11	48
11	49

The expected result for my Success case would be the distinct Table2.Table2_id value 11.

Answer 1

If you LEFT JOIN Table2 to Table1 and then GROUP BY table2_id, you can add a HAVING condition that compares the count of all Table2 rows with those having a matching Table1 row. If equal, all rows match and the table2_id can be included in the result.

Something like:

SELECT T2.table2_id
FROM Table2 T2
LEFT JOIN Table1 T1
    ON T1.table2_id = T2.table2_id
    AND T1.special_id = T2.special_id
GROUP BY T2.table2_id
HAVING COUNT(*) = COUNT(T1.Table1_id) -- No unmatched rows

If your ID list came from another source and you needed to verify that all matching Table2 rows for each ID had matching Table1 rows, you could use a NOT EXISTS() test as follows:

SELECT T.id
FROM Ids T
WHERE NOT EXISTS (
    SELECT *
    FROM Table2 T2
    LEFT JOIN Table1 T1
        ON T1.table2_id = T2.table2_id
        AND T1.special_id = T2.special_id
    WHERE T2.table2_id = T.id -- Current ID
    AND T1.Table1_id IS NULL -- No match
)

Note that the above will also pass if an ID has no matching Table2 rows. Since there are no Table2 rows, there are no unmatched rows, and the criteria passes. (Think of it like: What are the prerequisites for a class? None? Then, the prerequisite criteria is always satisfied and anyone can get in.)

See this db<>fiddle for a demo with some extra data.