Group by, having using where in condition in SQL Server

Question

SeqNo   ProfileId   MenuId
--------------------------
 1      P1001        1001
 2      P1001        1002
 3      P1001        1004
 4      P1001        1005
 5      P1001        1006
 6      P1001        1007
 7      P1001        1008
 8      P1001        1009
 9      P1001        1010
10      P1002        1001
11      P1002        1002
12      P1002        1003
13      P1002        1004
14      P1002        1005

The scenario is to check whether a given ProfileId exists or not for specific set of MenuId.

Suppose my input MenuId set is (1001, 1002, 1003, 1004, 1005), then it must return exact matching condition which is P1002.

If MenuId set is (1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010) then it must return P1001.

But if I use group by and where in condition, it returns both P1001, P1002.

How can I get exact matching condition for above scenarios?

Answer 1

If you want to match exact matching profiles, then one option is to aggregate and assert both the distinct and total counts:

SELECT ProfileId
FROM yourTable
GROUP BY ProfileId
HAVING
    SUM(CASE WHEN MenuId NOT IN (1001, 1002, 1003, 1004, 1005)
             THEN 1 ELSE 0 END) = 0 AND
    COUNT(DISTINCT MenuId) = 5 AND
    COUNT(*) = 5;

Demo

The three conditions in the HAVING clause assert that:

• No menu items other than 1001 to 1005 inclusive appear for that profile
• There are 5 distinct menu items for that profile
• There are only 5 total menu items for that profile