How to query and obtain a result set containing at minimum the SET of values submitted

WITH fData AS
(
    SELECT 1001 AS lookupID, 'A' AS LookUpValue UNION ALL
    SELECT 1001 AS lookupID, 'B' AS LookUpValue UNION ALL
    SELECT 1001 AS lookupID, 'C' AS LookUpValue UNION ALL
    SELECT 1001 AS lookupID, 'D' AS LookUpValue UNION ALL

    SELECT 1002 AS lookupID, 'A' AS LookUpValue UNION ALL
    SELECT 1002 AS lookupID, 'Z' AS LookUpValue UNION ALL
    SELECT 1002 AS lookupID, 'S' AS LookUpValue UNION ALL
    SELECT 1002 AS lookupID, 'J' AS LookUpValue UNION ALL

    SELECT 1003 AS lookupID, 'H' AS LookUpValue UNION ALL
    SELECT 1003 AS lookupID, 'I' AS LookUpValue UNION ALL
    SELECT 1003 AS lookupID, 'Z' AS LookUpValue UNION ALL
    SELECT 1003 AS lookupID, 'C' AS LookUpValue
) 


SELECT *
FROM fData fd
WHERE fd.LookUpValue IN ('A','B','C','D') /* This pulls back ANY record having these values and then return all of the records in that list.  I want ONLY lists that have a minimum of these values.  For example of my list only contained 'A' I would see both List 1001 records and List 1002 Records.  */

;

The code I provided simply contains three fake lists. Each list contains a set of values. How do I write a query that gives me all the records in the list that contain the values I specify. Using an IN statement doesn’t work as it gives me ALL the lists(records) that contain those list values. Instead I need returned only the list records that contain at least all of the values I specified.

In my example since I provided four values (‘A’,’B’,’C’,’D’) I should only see in my result set the records for the first list with lookupID 1001. If for example I submit a single value of ‘A’ I would then see all of the Records for List 1001 and List 1002 as both of those lists contain at minimum the value ‘A’. List 1003 does not have any values of ‘A’.

Answer

You get the lookupIDs that you want with this query:

SELECT lookupID
FROM fData
WHERE LookUpValue IN ('A','B','C','D')
GROUP BY lookupID
HAVING COUNT(DISTINCT LookUpValue) = 4 -- the number of searched lookupvalues

and all the rows of the table with the use of the operator IN:

SELECT * FROM fData
WHERE lookupID IN (
  SELECT lookupID
  FROM fData
  WHERE LookUpValue IN ('A','B','C','D')
  GROUP BY lookupID
  HAVING COUNT(DISTINCT LookUpValue) = 4
)

The code is standard SQL and works in any database (that I know of).
See the demo.
Results:

> lookupID | LookUpValue
> -------: | :----------
>     1001 | A          
>     1001 | B          
>     1001 | C          
>     1001 | D

Advertisement

Answer