Query table with composite primary key, excluding specific rows-CodePudding

product_id | category
---------------------
1          | A
1          | B
2          | A
3          | B

I would like to know which unique ids belong to category A but NOT category B. In the example above, only id: 2 would be a valid result

I came up with this which does not work as intended:

SELECT id
FROM table
WHERE category = 'A' AND category <> 'B'
GROUP BY id

A valid approach would be to use a subquery

SELECT id
FROM table
WHERE category = 'A' 
  AND id NOT IN (SELECT id
                 FROM table
                 WHERE category = 'B')

Is it possible to do this with a single query?

CodePudding user response：

Use a self-join:

SELECT t1.id
FROM table AS t1
LEFT JOIN table AS t2 ON t1.id = t2.id AND t2.category = 'B'
WHERE t1.category = 'A' and t2.id IS NULL

Another way is with grouping:

SELECT id
FROM table
WHERE category IN ('A', 'B')
GROUP BY id
HAVING MAX(category) = 'A'

CodePudding user response：

Use aggregation and the conditions in the HAVING clause:

SELECT id
FROM tablename
GROUP BY id
HAVING SUM(category = 'A') > 0
   AND SUM(category = 'B') = 0;

or:

SELECT id
FROM tablename
WHERE category IN ('A', 'B')
GROUP BY id
HAVING SUM(category = 'B') = 0;