List name that have every items in MsSQL

Question

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.

score 6 · Accepted Answer · answered Oct 22 '17 at 10:14

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

List name that have every items in MsSQL

1 Answers1