How to check if an entry of Table A contains all values of Table B-CodePudding

I was wondering if there is a proper way to check if one entry contains all values of another table.

The exercise is described as follows:

Get market numbers MNUM for markets supplied with at least all vegetables available from c1.

This is the table containing all the deliveries.

Here I get all the vegetables supplied by c1.

SELECT 
    VNUM
FROM
    CVM
WHERE 
    CNUM = 'c1'
GROUP BY 
    VNUM;

But I really don't know how I could check if m1 for example, supplied with all these...

Thank you in advance!

The result should be:

MNUM
--------
m2

Because m2 is supplied by v1,v2,v9

CodePudding user response：

First, get all vegetable numbers supplied by c1. Then select all rows from the table that contain those vegetables. Now aggregate. Group by market number and see if you get the complete number of vegetables for a market.

WITH c1_vnums AS
(
  SELECT vnum
  FROM cvm
  WHERE cnum = 'c1'
)
SELECT mnum
FROM cvm
WHERE vnum IN (SELECT vnum FROM c1_vnums)
GROUP BY mnum
HAVING COUNT(DISTINCT vnum) = (SELECT COUNT(DISTINCT vnum) FROM c1_vnums)
ORDER BY mnum;