Finding a list inside a list

Question

I'm having an issue with finding related database rows that aren't specifically linked. Lets say we have 5 tables.

Products
Id | name

Product_tags
Id | product_id | tag_id

Tags
Id | name

Blog 
Id | name

Blog_tags
Id | blog_id | tag_ids

So what I do is I grab a product, with this grab the tags this product has via the Product_tags table. Say I get something like this.

Product 101
Tags 123,124,125,126

Now I have this product page. The thing is, now I want to find a blog if it fits this product, but it doesn't need to have all tags that product has, product just needs to have all that blog has. So if blog has tags 124 and 126 it needs to match. Currently I save all the tags a blog has in one row (comma seperated) but this could be changed to save 1 tag per row if needed. If it was reversed I could do it but I need to check for a partial list in a different table.

Product 101
Tags 123,124,125,126

Should find
Blog 201
Tags 124,126

But not 
Blog 202
Tags 120,124

I tried a few ways but I couldn't find a way to make this work, my closest attempt was a reverse like like this.

select * from Blog_tags where "'".implode(",",$tagIds)."'" LIKE concat("%", tag_ids, "%")

This sort-of worked but not for when the product had 1,2,3 and the blog only had 1,3.

Answer 1

This is untested and only from memory, so feel free to comment and tell me if it doesn't work.

First of all, normalize your tag list, do not use comma-separated string. You said this could be changed, so I'm going work to assume it's one row per tag, and the column name is tag_id.

Let's start by finding the blogs having some of the tags of you product 101.

SELECT *
FROM Blog
JOIN Blog_tags ON Blog.Id = Blog_tags.blog_id
WHERE tag_id IN (123,124,125,126)
GROUP BY Blog.Id

Now this query will also include blogs that have tags not in this list. So we need to remove them. I think something like that could work:

SELECT *
FROM (
  SELECT Blog.id as blogId
  FROM Blog
  JOIN Blog_tags ON Blog.Id = Blog_tags.blog_id
  WHERE tag_id IN (123,124,125,126)
  GROUP BY Blog.Id
) as Blog_filtered
LEFT JOIN Blog_tags ON Blog_filtered.blogId = Blog_tags.blog_id AND Blog_tags.tag_id NOT IN (123,124,125,126)
WHERE Blog_tags.id IS NULL
GROUP BY Blog_filtered.blogId