Tutorial :MySql scoping problem with correlated subqueries



Question:

I'm having this Mysql query, It works:

SELECT       nom      ,prenom      ,(SELECT GROUP_CONCAT(category_en) FROM              (SELECT DISTINCT category_en FROM categories c WHERE id IN                  (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = 37)              ) cS        ) categories      ,(SELECT GROUP_CONCAT(area_en) FROM               (SELECT DISTINCT  area_en FROM areas c WHERE id IN                   (SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = 37)              ) aSq       ) areas  FROM m3allems m  WHERE m.id = 37       

The result is:

nom             prenom      categories              areas  Man             Multi       Carpentry,Paint,Walls   Beirut,Baalbak,Saida  

It works correclty, but only when i hardcode into the query the id that I want (37). I want it to work for all entries in the m3allem table, so I try this:

SELECT       nom      ,prenom      ,(SELECT GROUP_CONCAT(category_en) FROM              (SELECT DISTINCT category_en FROM categories c WHERE id IN                  (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = m.id)              ) cS        ) categories      ,(SELECT GROUP_CONCAT(area_en) FROM               (SELECT DISTINCT  area_en FROM areas c WHERE id IN                   (SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = m.id)              ) aSq       ) areas  FROM m3allems m  

And I get an error:

Unknown column 'm.id' in 'where clause'

Why? From the MySql manual:

13.2.8.7. Correlated Subqueries   [...]   Scoping rule: MySQL evaluates from inside to outside.  

So... do this not work when the subquery is in a SELECT section? I did not read anything about that.

Does anyone know? What should I do? It took me a long time to build this query... I know it's a monster query but it gets what I want in a single query, and I am so close to getting it to work!

Can anyone help?


Solution:1

You can only correlate one level deep.

Use:

   SELECT m.nom,            m.prenom,            x.categories,            y.areas       FROM m3allens m  LEFT JOIN (SELECT m2c.m3allem_id,                    GROUP_CONCAT(DISTINCT c.category_en) AS categories               FROM CATEGORIES c               JOIN m3allems_to_categories m2c ON m2c.category_id = c.id           GROUP BY m2c.m3allem_id) x ON x.m3allem_id = m.id  LEFT JOIN (SELECT m2a.m3allem_id,                    GROUP_CONCAT(DISTINCT a.area_en) AS areas               FROM AREAS a               JOIN m3allems_to_areas m2a ON m2a.area_id = a.id           GROUP BY m2a.m3allem_id) y ON y.m3allem_id = m.id      WHERE m.id = ?  


Solution:2

The reason for the error is that in the subquery m is not defined. It is defined later in the outer query.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »