MySql: Check where clause for all row returned by an inner join

Question

I have this query

SELECT *
FROM posts
INNER JOIN categories ON categories.post_id = posts.id
INNER JOIN tags ON tags.category_id = categories.id
WHERE tags.title = 'week_trend'

Each posts has multiple categories and also each category has multiple tags and I need the posts that have the categories with the specified tag but all the post categories should have this condition and even if one of those categories failed the condition the post shouldn't be included. My query returns the posts even if one of their categories has the specified tag.

I almost have no idea how to do it can someone help me tnx

Answer 1

This query:

SELECT c.post_id
FROM categories c INNER JOIN tags t
ON t.category_id = c.id
GROUP BY c.post_id
HAVING COUNT(DISTINCT c.id) = SUM(t.title = 'week_trend')

returns all the post_ids with categories that are all related to the tag with title 'week_trend'.
Use it with an IN clause:

SELECT *
FROM posts 
WHERE id IN (
  SELECT c.post_id
  FROM categories c INNER JOIN tags t
  ON t.category_id = c.id
  GROUP BY c.post_id
  HAVING COUNT(DISTINCT c.id) = SUM(t.title = 'week_trend')
)