I have a scenario to find the CategoryId which all have the mentioned Mode values. The schema of the table ModeTester:CREATE TABLE ModeTester (    ModeId      INT IDENTITY(1, 1) NOT NULL,    CategoryId  INT,    Mode        CHAR(1),    ModeValue   VARCHAR (20))The dummy values:INSERT INTO ModeTester (CategoryId, Mode, ModeValue)SELECT 1, 'H', 'Hospital' UNIONSELECT 2, 'W', 'Work' UNIONSELECT 1, 'R', 'Rating' UNIONSELECT 3, 'W', 'Window' UNIONSELECT 3, 'H', 'Home' UNIONSELECT 3, 'R', 'Rail' UNIONSELECT 4, 'R', 'Royal' UNIONSELECT 4, 'H', 'Hide' UNIONSELECT 4, 'W', 'Work'From the above data, I want to get the result of CategoryId those should have all Mode values W, R, H.So, I wrote this querySELECT C.CategoryIdFROM (    SELECT CategoryId, Count(CategoryId) [CategoryCount]    FROM ModeTester     WHERE Mode IN ('H', 'W', 'R')    GROUP BY CategoryId) CWHERE C.CategoryCount = 3It returns the expected data:CategoryId---------- 3 4But, I want to know if there are any better ways to achieve this or fine-tune the query.Here's a sample SQL Fiddle.Hard-coding the CategoryCount = 3 is not helped on all cases. If the data increases, checking on category count condition is not working. 
Find a Id that contains all mentioned values
sql;sql server
The CategoryCount hard-coding is not work for all cases. So i find another option by using INTERSECT.SELECT DISTINCT CategoryId FROM ModeDetails WHERE Mode = 'W'INTERSECTSELECT DISTINCT CategoryId FROM ModeDetails WHERE Mode = 'H' INTERSECTSELECT DISTINCT CategoryId FROM ModeDetails WHERE Mode = 'R'Working SQL FiddleUpdate: Since the INTERSECT returns the expected result, but due to the bulk data its take more time to return the result. Adding the DISTINCT in HAVING clause solves the issue. The working code:SELECT CategoryIdFROM ModeTester WHERE Mode IN ('H', 'W', 'R')  GROUP BY CategoryIdHAVING COUNT(DISTINCT Mode) = 3 The sample working fiddle