SQL - Combining in and not in-CodePudding

I have a SQL that shows me PRODUCTS containing 3 specific PARTS. I would like to add a condition that queries PRODUCTS containing PARTS A,B,C but not PART D:

Existing QUERY:

SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c')
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;

Subquery?

Thank you for any help!

CodePudding user response：

Can you use NOT EXISTS with a correlated nested query?

SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c') AND NOT EXISTS (
    SELECT * from your_table_name yt_inner
    WHERE yt_inner.Part = 'd' and
        yt_inner.Product = your_table_name.Product)
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;

CodePudding user response：

Other way.

AsIs:
SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c')
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;

ToBe:
SELECT a.Product
FROM (
  SELECT a.Product
    , SUM(CASE WHEN a.Part = 'a' THEN 1 ELSE 0 END) AS A_YN
    , SUM(CASE WHEN a.Part = 'b' THEN 1 ELSE 0 END) AS B_YN
    , SUM(CASE WHEN a.Part = 'c' THEN 1 ELSE 0 END) AS C_YN
    , SUM(CASE WHEN a.Part = 'd' THEN 1 ELSE 0 END) AS D_YN
  FROM your_table_name a
  GROUP BY a.Product
) a
WHERE 1=1
AND (A_YN   B_YN   C_YN) = 3
AND D_YN = 0