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?

Andomar · Accepted Answer · 2011-08-28 10:35:23Z

3

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

answered Aug 28, 2011 at 10:35

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arnaud Le Blanc · Accepted Answer · 2011-08-28 10:44:35Z

0

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.

edited Aug 28, 2011 at 10:44

answered Aug 28, 2011 at 10:38

Arnaud Le Blanc

100k24 gold badges211 silver badges196 bronze badges

4 Comments

Andomar Over a year ago

Without count(distinct ...), it will also return posts that have tag 2 twice

Arnaud Le Blanc Over a year ago

I was assuming that a post couldn't have twice the same tag

Andomar Over a year ago

With a unique constraint on (tag_id, post_id) this would work fine

baranq Over a year ago

Yes, one post can't have same tag twice.

Collectives™ on Stack Overflow

Problem with mysql query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related