Find a Id that contains all mentioned values

Question

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.

Answer 1

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

Answer 2

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'
INTERSECT
SELECT DISTINCT CategoryId FROM ModeDetails WHERE Mode = 'H' 
INTERSECT
SELECT DISTINCT CategoryId FROM ModeDetails WHERE Mode = 'R'

Working SQL Fiddle

Update: 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 CategoryId
FROM ModeTester 
WHERE Mode IN ('H', 'W', 'R')  
GROUP BY CategoryId
HAVING COUNT(DISTINCT Mode) = 3 

The sample working fiddle

Answer 3

Couple of things that you can do :

1. Always use schema.ObjectName. Check Bad habits to kick : avoiding the schema prefix for more details.

   Without an explicit schema, SQL Server will first look under the schema associated with your login.

2. Below index will give you a nice seek for your query:

   CREATE NONCLUSTERED INDEX [nc_CId_Mode]
    ON [dbo].[ModeTester] ([Mode])
   INCLUDE ([CategoryId])
   GO
   

Note: You should take into consideration how often the query is executed, check your workload as well as keep into account that maintenance (reorg/rebuild) might be required for the index (its better than doing a table scan since you have a heap table - table without and index).