Combining multiple databases

Question

I have three tables: categories, content_infoand content.

• The table categoriescontains a category idand its category identifier parent.
• content_infocontains two columns: entry_idfor the message identifier and cat_idfor the post category identifier.
• The table contentcomprises several columns of column - for example id, titleetc.

I have a variable in the url called parent_id that matches the parentcategory. I want to list all POSTS (not CATEGORIES) that are categorized with the parentvalue of parent_id.

For example, the value of parent_id is 5. Each message may belong to a category with identifier 20, but this category belongs to the parent category (whose identifier is 5). I want to list all posts that belong to categories with the value parentof what was in the current parent_id.

Is there a way to do this with a MySQL join instead of modifying 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 returns all messages ( contentrows) that belong to the category whose parent isparent_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>)

Answer 2

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