I've three tables: categories, content_info, and content.

  • The categories table consists of the category's id and also the ID of their parent category.
  • The content_info consists of two posts: entry_id for that post's ID and cat_id for that ID from the post's category.
  • The content table consists of multiple posts concerning the publish - for example ID, title, etc.

I've got a variable within the URL known as parent_id which matches the parent of the category. I wish to list all of the POSTS (not Groups) which fit in with a category having a parent from the parent_id value.

For instance, the parent_id value is 5. Each publish might fit in with a category by having an ID of 20, but that category goes towards the parent category (whose ID is 5). I wish to list all of the posts who fit in with groups having a parent worth of regardless of the current parent_id is actually.

It is possible to method of carrying this out with MySQL joins rather than altering the PHP?

This will get it done:

SELECT c.* FROM content
    JOIN content_info ci ON ci.entry_id=c.id
    JOIN categories cat ON cat.id=ci.cat_id
WHERE cat.parent_id=<parent_id>

This return all posts (content rows) which fit in with a category which parent is parent_id

Or with subqueries:

SELECT c.* FROM content
JOIN content_info ci ON ci.entry_id=c.id
WHERE ci.cat_id IN (SELECT id 
                    FROM categories cat 
                    WHERE cat.parent_id=<parent_id>)
SELECT c.*
FROM content c,
     categories cat,
     content_info ci
WHERE c.id = ci.entry_id
AND   cat.id = ci.cat_id
AND   cat.parent_id = 5