Select ID with specific values in more than one field

user_15286053 0
Viewed 69

I have a table as follows

groupCode ProductIdentifier
1 dental
1 membership
2 dental
2 vision
2 health
3 dental
3 vision

I need to find out if a specific groupCode have "dental", "vision" and "health" (all three simultaneously)

The expected result is code 2

What I need to identify is if groupCode 2 has the three products (or two, or whatever the user enters). This is part of a huge kitchen sink query I'm building.

I'm doing

SELECT groupCode 
FROM dbo.table
WHERE (productIdentifier = N'dental') 
  AND (productIdentifier = N'vision') 
  AND (productIdentifier = N'health') 
  AND (groupCode = 2)

But clearly is wrong because it's not working.

I tried to do something like its described here but it didn't return a result for me:

Select rows with same id but different value in another column

Thanks.

sql
2 Answers

If each of 'dental','vision' and 'health' occur only once per group identifier, you can group by group identifier and filter by the groups having count(*) = 3:

WITH
-- your input ..
indata(groupCode,ProductIdentifier) AS (
          SELECT 1,'dental'
UNION ALL SELECT 1,'membership'
UNION ALL SELECT 2,'dental'
UNION ALL SELECT 2,'vision'
UNION ALL SELECT 2,'health'
UNION ALL SELECT 3,'dental'
UNION ALL SELECT 3,'vision'
)
-- real query starts here ...
SELECT
  groupcode
FROM indata
WHERE productidentifier IN ('dental','vision','health')
GROUP BY
  groupcode
HAVING COUNT(*) = 3;
-- out  groupcode 
-- out -----------
-- out          2
user_67882550

As per Marcothesane answer, if you know the groupCode (2) and the number of products (vision, dental and health), 3 in this case, and you need to confirm if that code has those three specific products, this will work for you:

SELECT        COUNT(groupCode) AS totalRecords
FROM            dbo.table
WHERE       (groupCode = 2) AND (productIdentifier IN ('dental', 'vision', 'health'))
HAVING        (COUNT(groupCode) = 3)

This will return 3 (number of records = number of products).

Its basically's Marcothesane answer in a way you can "copy/paste" to your code by just changing the table name. You should accept Marcothesane answer.

user_148677490
Related