MySQL id merge question

Sorry for the vague topic but I'm having a hard time explaining this. What I'm trying to do is fetching an ID representing a post which can be posted in different categories, and I want my post to belong to all three categories to match the criteria. T开发者_Go百科he table looks like this

id      category_id
1            3
1            4
1            8

What I wanna do is fetch an id that belongs to all 3 of these categories, but since they're on different rows I can't use

SELECT id FROM table WHERE category_id = '3' AND category_id = '4' AND category_id = '8';

This will of course return nothing at all, since no row matches that criteria. I've also tried with the WHERE IN clause

 SELECT id from table WHERE category_id IN (3, 4, 8);

This returns any post in any of these categories, the post I want returned has to be in all three of these categories.

So the question becomes, is there any good way to look for an id that belongs to all three of these categories, or do I have to use the WHERE IN clause and see if I get 3 rows with the id 1, then I'll know that it occured three times, therefor belongs to all three categories, seems like a bad solution.

Thanks in advance. I appreciate the help. :)

You could use group_concat to get a comma separated string of your category id's and check if that contains all the categories you're filtering

Update

SELECT t1.id, GROUP_CONCAT(t2.category_id) as `categories`
FROM `table` AS t1
INNER JOIN `table` AS t2 ON t1.id = t2.id
HAVING
FIND_IN_SET('3', `categories`) IS NOT NULL AND
FIND_IN_SET('4', `categories`) IS NOT NULL AND
FIND_IN_SET('8', `categories`) IS NOT NULL

The last query would not have worked, since it is a group function the value cannot be used in the WHERE clause but can be used in the HAVING clause.

I think you need to say GROUP BY id like this:

SELECT id FROM table WHERE category_id = '3' OR category_id = '4' OR category_id = '8'; GROUP BY id;

Hope that works for you.

i believe you need to establish the amount of categories a post appears in.

this can be done by applying COUNT and HAVING to the SQL query as follows:

SELECT id,
       COUNT(category_id) AS categories 
FROM `table` 
GROUP BY id 
HAVING categories > 3

and if you wish the number of categories in your site to change dynamically you can always have an inner SELECT statement like so:

SELECT id,
       COUNT(category_id) AS categories 
FROM `table` 
GROUP BY id 
HAVING categories > (
                     SELECT COUNT(category_id) 
                     FROM `categories`
                     )

where categories is the table you store all your categories information