Problem with mysql query

Question

i have problem with mysql query and i'm stuck...

I have post with many tags and i want to let users to get posts with choosen tags. I store information about tags in table like this:

Tag_warp(
id_tag_wrap,
id_tag,
id_post)

I need to select distinct post having selected tags. Do you have idea how to make it? If i ask for: id_tag = "5" AND id_tag= "2" i got no results (which is obvious) if i ask for: id_tag = "5" OR id_tag= "2" i have id_post with results having 2 or 5 but i need only those post having both id_tag.

Any advice?

Answer 1

You can do it like this:

SELECT * FROM Tag_warp
WHERE tag_id IN (2, 5)
GROUP BY post_id
HAVING COUNT(post_id) = 2

This will return posts that have both tags 2 and 5.

Answer 2

You can group by post, and demand that each post has both tags:

select  id_post
from    tag_warp
where   id_tag in (2,5)
group by
        id_post
having  count(distinct id_tag) = 2