3

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.

Raj
  • 22,346
  • 14
  • 99
  • 142
Miroslav Vasilev
  • 31
  • 1
  • 2
  • See a question with more than 10 ways to achieve this (and performance tests): **[How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation)** – ypercubeᵀᴹ Dec 03 '12 at 15:36

2 Answers2

5

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;
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thank you very much.. it did the work for me. I have one more question tho.. its about the same table... How can I select only post id's that are associated ONLY to category id 5 and not to any other. – Miroslav Vasilev Dec 03 '12 at 21:45
  • 1
    In the first query the `SELECT posts_id, category_id` should be only `SELECT posts_id` – ypercubeᵀᴹ Dec 03 '12 at 22:18
  • For some reason the second example doesnt work :(.. I get ID's that are associated to category_id 5, but they are also associated to other categories... – Miroslav Vasilev Dec 03 '12 at 22:40
  • @MiroslavVasilev: I updated my answer again. Now it should work. – juergen d Dec 03 '12 at 22:51
  • Does the second query mean the same thing with `SELECT posts_id FROM posts GROUP BY posts_id HAVING SUM(categoryId NOT IN (5)) = 0;` ? Just in case we have a combination of categories – Prabowo Murti May 02 '16 at 13:28
  • I use other query to get a post with the EXACT combination of categories. In my case, it is not possible for two or more posts to have the same combination of categories. From the data from the table above, the post_id 1 and 3 can not share the same combination of categories (category 2 and 3). I add another row : `post_id = 3`, `category_id = 5` to the table. The query to get a post with category 2 and 3 : `SELECT post_id, COUNT(DISTINCT category_id) AS category_counter FROM posts GROUP BY post_id HAVING SUM(category_id NOT IN (2,3)) = 0 AND category_counter = 2;` Do you have any better idea? – Prabowo Murti May 03 '16 at 04:05
0
SELECT post_id, category_id
FROM posts
WHERE category_id IN (2, 3)
GROUP BY post_id
ORDER BY category_id;
Raghvendra Parashar
  • 3,883
  • 1
  • 23
  • 36