Question 113248 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find records where we have both values in child records

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

POSTS table

ID TITLE
101 Something related to AUSTRALIA and CRICKET
102 Something related to INDIA and CRICKET
103 Something related to CRICKET ALONE
104 Something related to INDIA ALONE

TAGS table

ID POSTS_ID TAG_NAME
1001 101 CRICKET
1002 101 AUSTRALIA
1003 102 CRICKET
1004 102 INDIA
1005 103 CRICKET
1006 104 INDIA

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:

ID TITLE
102 Something related to INDIA and CRICKET

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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 🎉


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search