How to add additional tables/fields into a query with multiple inner joins?

Question

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?

SELECT desired_categories.* 
FROM
    ( SELECT nid, title, COUNT(1) AS all_categories 
        FROM node 
        GROUP BY nid
    ) AS A 

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories 
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid 
        IN (498,499,500) 
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN 
    ( SELECT DISTINCT nid, title 
        FROM node 
    ) AS desired_categories USING (nid)

WHERE all_categories = good_categories;

I have about 5 fields from 5 other tables I need access to. All tables use the node id nid to indicate their relationship. I suspect it's possible to simply add a join in for each table but I haven't had any luck yet.

Answer 1

After further tinkering, I worked it out. If I'm correct, then it's stupidly simple.

Additional tables can be added to my scenario if:

1. they are added as LEFT JOINS
2. they are added after the INNER JOINS

I'm still trying to wrap my head around exactly how JOIN functions, but it makes sense to me that the first three joins are narrowing down the selection. In order to expand the selection (to add my additional tables/values) I use the LEFT JOIN, which essentially says, "use everything to the left (my inner joins) and use this table too."

SELECT desired_categories.*, t1.valueX, t2.valueX, t3.valueX
FROM
    ( SELECT nid, title, additional_col_1, additional_col_2, COUNT(1) AS all_categories 
        FROM node 
        GROUP BY nid
    ) AS A 

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories 
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid 
        IN (498,499,500) 
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN 
    ( SELECT DISTINCT nid, title 
        FROM node 
    ) AS desired_categories USING (nid)

LEFT JOIN table_1 AS t1 ON desired_categories.nid = t1.entity_id
LEFT JOIN table_2 AS t2 ON desired_categories.nid = t2.entity_id
LEFT JOIN table_3 AS t3 ON desired_categories.nid = t3.entity_id
LEFT JOIN table_4 AS t4 ON desired_categories.nid = t4.entity_id
LEFT JOIN table_5 AS t5 ON desired_categories.nid = t5.entity_id

WHERE all_categories = good_categories
  AND A.additional_col_1 > t1.valueX
  AND A.additional_col_2 = 'active';