MySql: Select all messages with Category ID = 2 and 3

advertisements

I have the following database with 2 columns:

posts id    category id
1           2
2           1
1           3
3           3
3           2
4           5

I need a query that when extracts all post id's that are in category 2 AND 3 which means : posts with id: 1 and 3.


to select all post id's that are in category 2 AND 3:

SELECT posts_id
FROM posts
WHERE categoryId IN (2, 3)
GROUP BY posts_id
HAVING COUNT(distinct categoryId) = 2;

to select only post id's that are associated ONLY to category_id = 5 and not to any other:

SELECT posts_id
FROM posts
GROUP BY posts_id
HAVING SUM(categoryId <> 5) = 0;