Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
+1 for looking the answer on your own. –  RolandoMySQLDBA May 10 '13 at 19:22

1 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';
share|improve this answer
    
+1 for further tinkering !!! –  RolandoMySQLDBA May 10 '13 at 19:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.