Find the codes of dealers who sell a certain part to all projects-CodePudding

I have a table "stats" that consists 3 ids.

IDs: id_seller, id_part and id_proj

From this table, I want to return id_sellers that sell a certain part to all projects.

In other words: return id_seller which id_part has all id_projs values.

The result should look like this:

In the table, id_part value P3 is the only part that has all id_projs values WHICH are all ('J1', 'J2', 'J3', 'J4', 'J5', 'J6', 'J7').

test code:

SELECT id_seller, id_part, id_proj
FROM stats
WHERE id_seller = 'S2' AND id_part = 'P3' AND EXISTS (
      SELECT 1
      FROM stats
      WHERE id_proj IN ('J1', 'J2', 'J3', 'J4', 'J5', 'J6', 'J7'));

I have tried subqueries, Having count, cases ext. to solve the problem, but with no luck.

Looked for some kind of ONLY IN keywords, that would return id_parts that only contain all values. not if they contain only one or some.

CodePudding user response：

There is probably better solutions, but I think something like this should work:

SELECT s.id_seller
FROM stats s
WHERE s.id_part = 'P3'
AND 
(
   SELECT COUNT(DISTINCT id_proj)
   FROM stats s2
   WHERE s2.id_seller = s1.id_seller
   AND s2.id_part = s1.id_part
) = 7