How to check if a Entrie of Table A, contains all Values of Table B

byMR

December 10, 2022

I was wondering if there is a proper way to check if one entrie contains all values of an other table.

The exercise is described as followed:

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.

All Vegetables supplied by c1

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

But I really don’t know how I could checkt if m1 for example, supplied with all these…

Thank you in advanced!

The Result should be:

MNUM

Because m2 is supplied by v1,v2,v9

>Solution :

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;