sql server - Is there a simple way to retrieve combinations that contain a list of ids?

Question

Here is the input and the data to search thru. Trying to find SourceCombinationIDs that contain SourceID 62 and 67. So far I only have a query that returns SourceCombinationIds that contain either 62 or 67 but I need to get SourceCombinationIds that contain both - 62 and 67 (they may contain more sources, but they have to have at least these two).

Thank you in advance!

DECLARE @SourceIDs_In TABLE (SourceID INT);
INSERT INTO @SourceIDs_In VALUES (62), (67);

-- data to search
DROP TABLE IF EXISTS #All;
CREATE TABLE #All ( [SourceId] INT, [SourceCombinationId] INT )
INSERT INTO #All ([SourceId], [SourceCombinationId])
VALUES
    (67, 80), 
    (66, 81), 
    (68, 82), 
    (61, 82), 
    (62, 83), --
    (67, 83), --
    (68, 84), 
    (62, 85), 
    (64, 86), 
    (69, 87), 
    (65, 88), 
    (60, 88), 
    (67, 88), 
    (67, 89), --
    (62, 89), --
    (66, 89); --

-- expected result
/*
[SourceCombinationId]
    83
    89
*/

SELECT * FROM #All ORDER BY SourceCombinationId, SourceId;

-- this returns SourceCombinationId that contain either 62 or 67 but not both
SELECT DISTINCT A.SourceCombinationId FROM #All A
INNER JOIN @SourceIDs_In SIDI ON SIDI.SourceID = A.SourceId
;

question from:https://stackoverflow.com/questions/65831760/is-there-a-simple-way-to-retrieve-combinations-that-contain-a-list-of-ids

Answer 1

You can compare the number of hits per SourceCombinationId to see if it matches the total count of @SourceIDs_In. I.e. if less than all the matches, then the SourceCombinationId did not have all the source ids.

SELECT A.SourceCombinationId,
    count(*) TotalHits
FROM (
    SELECT DISTINCT *
    FROM #All
    ) A
INNER JOIN @SourceIDs_In SIDI ON SIDI.SourceID = A.SourceId
GROUP BY A.SourceCombinationId
HAVING count(*) >= (
        SELECT count(*)
        FROM @SourceIDs_In
        )