Check if multiple values ALL EXIST in a table-CodePudding

SQL, SQL Server 2016

I've got a table "Characteristics" (from a catalog) and for a product (that comes with a list of characteristics). I need to check, if every item of the list is contained in Characteristics.

Only if all items of the list are present in the table, the catalog is considered valid.

The List of characteristics is simply a table with

ID               CHARACTERISTIC
1                Blue  
1                Yellow  
1                Big  
2                Pointy  
...

For one item I can do a query like:

SELECT CatalogNumber FROM CHARACTERISTICS  
WHERE EXISTS (SELECT * FROM CHARACTERISTICS WHERE Item = ID AND CHARACTERISTIC = 'Characteristic1') 
  AND EXISTS (SELECT * FROM CHARACTERISTICS WHERE Item = ID AND CHARACTERISTIC = 'Characteristic2')

But since the number of characteristics for each item in the list is different for each item, this approach doesn't work.

Is there a way to check, if all characteristics are in the catalog without resorting to a cursor and a loop?

Thank you in advance

Wolfgang

CodePudding user response：

Select id from Characteristics 
  group by id 
  having count(*) = (select count(distinct Characteristic) from Characteristics);

DBfiddle demo