Search code examples
sqlsql-servert-sqlrelational-division

List name that have every items in MsSQL

TableA
Name Items
  A  1
  A  2
  A  2
  A  3
  A  3
  B  1
  B  1
  B  2
  B  2
  C  1
  C  2
  C  2
  C  3

TableB
Items ItemsName
   1  One
   2  Two
   3  Three

I want to list 

Name
A
C

Because A and C have every items in TableB. And B doesn't have 3. I think I should use "NOT EXISTS" maybe.

Solution

  • This is one way of doing it:

    SELECT a.Name
FROM TableB AS b
JOIN TableA as a ON b.Items = a.Items
GROUP BY a.Name
HAVING COUNT(DISTINCT b.Items) = (SELECT COUNT(DISTINCT Items) FROM TableB)

    The query joins records of TableB with records of TableA. Using an INNER JOIN we can find matching records of TableA.

    The HAVING clause checks whether the number of distinct Items values is equal to the population if TableB. Only a.Name values related to all Items values of TableB are returned.

    Demo here