Multiple database joins

Question

I have three tables: categories, content_info, and content.

• The categories table contains the category's id and the ID of its parent category.
• The content_info contains two columns: entry_id for the post's ID and cat_id for the ID of the post's category.
• The content table contains multiple columns about the post - such as ID, title, etc.

I have a variable in the URL called parent_id which corresponds to the parent of a category. I want to list all the POSTS (not CATEGORIES) which belong to a category with a parent of the parent_id value.

For example, say the parent_id value is 5. Each post might belong to a category with an ID of 20, but that category belongs to the parent category (whose ID is 5). I want to list all the posts who belong to categories with a parent value of whatever the current parent_id happens to be.

Is there a way of doing this with MySQL joins instead of changing the PHP?

Answer 1

This should do it:

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 belong to 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>)