Find a Id that contains all mentioned values 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' UNION
SELECT 2, 'W', 'Work' UNION
SELECT 1, 'R', 'Rating' UNION
SELECT 3, 'W', 'Window' UNION
SELECT 3, 'H', 'Home' UNION
SELECT 3, 'R', 'Rail' UNION
SELECT 4, 'R', 'Royal' UNION
SELECT 4, 'H', 'Hide' UNION
SELECT 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 query

SELECT C.CategoryId
FROM (
    SELECT CategoryId, Count(CategoryId) [CategoryCount]
    FROM ModeTester 
    WHERE Mode IN ('H', 'W', 'R')
    GROUP BY CategoryId
) C
WHERE C.CategoryCount = 3


It returns the expected data:




CategoryId
----------
 3
 4



But, 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.
You can use a HAVING clause to filter your GROUP BY result.

SELECT CategoryId
FROM ModeTester 
WHERE Mode IN ('H', 'W', 'R')
GROUP BY CategoryId
HAVING Count(CategoryId) = 3