seo Question posted in
Some basic information to get you started with this can be found here.

MySQL query for a many-to-many relationship table – SEO

i have some problems here and i need help.

I have a table called posts, another table called tags and many-to-many relationship between them called item_tags

Here is the strucutre:

posts

  • id
  • title
  • description

tags

  • id
  • name
  • seo

item_tags

  • id
  • post_id
  • tag_id

So let’s say now i want to build a query to match multiple tags by having their tag_id already. For an example let’s try to match all posts which has tag_id 11, tag_id 133 and tag_id 182. What i could do was selecting them with OR operator but these are not the results i want because what i want is to match all posts which has all mentioned tags only not just if contains some…

My query was:

SELECT * FROM item_tags WHERE tag_id=’11’ OR tag_id=’133′ OR tag_id=’182′

Which is wrong…

Here is a screenshot of the table: https://i.imgur.com/X60HIM5.png

PS: I want to build a search based on multiple keywords (tags).

Thank you!

Tags:

3

Answers


  1. 0

    If you want all posts that have been tagged with all three tags, then you could use:

    select p.*
from posts p
where exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 11)
  and exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 133)
  and exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 182)

    If you want posts tagged with any of those three tags use:

    select p.*
from posts p
where exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 11)
   or exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 133)
   or exists (select 1 from item_tags a where a.post_id = p.id and a.tag_id = 182)
    Login or Signup to reply.
  2. 0

    Important Note: I have not tested it!

    SELECT * FROM posts WHERE id IN(SELECT post_id FROM item_tags WHERE item_tags.tag_id='11' OR item_tags.tag_id='133' OR item_tags.tag_id='182');
    Login or Signup to reply.
  3. 0

    You can group result by post_id and then save only those having all tags linked

    select * 
from posts 
where id in (
    select post_id 
    from item_tags 
    where tag_id in (11,133,182) 
    group by post_id 
    having count(tag_id)=3
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search