I have a posts table where tags are saved in another table. And the structure is as following.

POSTS table

TAGS table

Is there any way we can get the posts_id based on the combination of tags ? For example, I would like to get the posts_id where tags CRICKET & INDIA is present.

expected result:

the table have approximately a million records. So the query has to be optmised.

Its a bit tricky for me to solve this. I would be really thankful if someone can help me on this.

CodePudding user response：

You can simply do it like this:

Select POSTS.* From POSTS 
INNER JOIN TAGS on POSTS.ID = TAGS.POSTS_ID
where TAGS.TAG_NAME LIKE '%INDIA%' OR TAGS.TAG_NAME LIKE '%CRICKET%'

you can modify where clause, this is based on what I understood from your question

CodePudding user response：

So I think I have found the answer.

select posts.id, posts.title from posts
 join (
   select posts_id from post_tags
   where TAG_NAME in ('INDIA','CRICKET')
   group by posts_id
   having count(DISTINCT(TAG_NAME)) > 1
 ) as c_post_tags
 on posts.id = c_post_tags.posts_id

And I have made the post_tags.TAG_NAME as an INDEX. so that the performance is much faster.

Thank you all for your efforts