I have a table as follows

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.

CodePudding user response：

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                                                                                                                                                                                                                          

CodePudding user response：

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.