SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (
    Table1_id INT IDENTITY(1,1),
    Table2_id INT,
    special_id INT
)
INSERT Table1
VALUES
    -- Case 1: Fail (2 of 5 matches)
    (11, 47), (11, 48),
    -- Case 2: Pass (5 of 5 matches)
    (12, 45), (12, 46), (12, 47), (12, 48), (12, 49),
    -- Case 3: Pass (1 of 1 matches)
    (13, 50),
    -- Case 4: Fail (0 of 1 matches)
    -- Case 5: Pass (2 of 2 matches, with ignored dups and extras )
    (15, 70), (15, 71), (15, 72), (15, 72), (15, 73)  -- 2 extra and 1 dup

CREATE TABLE Table2 (
    Table2_id INT,
    special_id INT
)
INSERT Table2
VALUES
    -- Case 1: Fail (2 of 5 matches)
    (11, 45), (11, 46), (11, 47), (11, 48), (11, 49),
    -- Case 2: Pass (5 of 5 matches)
    (12, 45), (12, 46), (12, 47), (12, 48), (12, 49),
    -- Case 3: Pass (1 of 1 matches)
    (13, 50),
    -- Case 4: Fail (0 of 1 matches)
    (14, 60),
    -- Case 5: Pass (2 of 2 matches, with ignored extras)
    (15, 71), (15, 72)
27 rows affected

-- Unfiltered results
SELECT
    T2.table2_id,
    COUNT(*) AS CountAll,
    COUNT(T1.Table1_id) AS CountJoin
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


table2_id
CountAll
CountJoin


11
5
2

12
5
5

13
1
1

14
1
0

15
3
3


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Filtered Results
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


table2_id


12

13

15


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Separate ID source
CREATE TABLE Ids (
    id INT
)
INSERT Ids
VALUES
    (11), -- Case 1: Fail (2 of 5 matches)
    (12), -- Case 2: Pass (5 of 5 matches)
    (13), -- Case 3: Pass (1 of 1 matches)
    (14), -- Case 4: Fail (0 of 1 matches)
    (15), -- Case 5: Pass (2 of 2 matches, with ignored extras)
    (16)  -- Case 6: *** Also a Pass (0 of 0 matches = no failed matches) ***

6 rows affected

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
)


id


12

13

15

16