SQL query many-to-many association table that have only one single associated item-CodePudding

I have promotionCategories table below;

 ---- -------------- ------------- 
| id | promotion_id | category_id |
 ---- -------------- ------------- 
|  1 |            1 |           1 |
|  2 |            2 |           1 |
|  3 |            2 |           2 |
|  4 |            3 |           1 |
|  5 |            4 |           2 |
|  6 |            4 |           3 |
|  7 |            5 |           2 |
 ---- -------------- -------------

I have no idea how to query something like

Any promotion that have only one category and it is category_id = 1

expect result id 1, 4 which there are promotion_id 1,3 that have only single category_id = 1

CodePudding user response：

We can use aggregation here:

SELECT promotion_id
FROM yourTable
GROUP BY promotion_id
HAVING MIN(category_id) = MAX(category_id) AND  -- only one category
       MIN(category_id) = 1;                    -- that category is 1