MySQL:Old-Style JOIN Syntax is Required for Correlated Subqueries

Notice in the query below, the JOIN/ON syntax we’re all comfortable with does NOT work for correlating subqueries. You must use the old WHERE/= style of joining tables. For example:

    (        # The old-style join syntax used here is REQUIRED for the correlated subquery:
            SELECT COUNT(DISTINCT as uus_count
            FROM user_unit_subscription uus, sims_organization so, user u, sims_user su         
            WHERE = c.sims_organization_id
            AND uus.license_unit_id =
            AND = uus.user_id
            AND = u.sims_id
            AND su.enable = 1
            AND so.deleted = 0
            AND su.organization_id =         
            GROUP BY uus.license_unit_id
     ) as correct_sub_count
FROM customer_order_subscription cos
JOIN customer_order co 
ON cos.customer_order_id =
JOIN customer c 
ON co.customer_id =
JOIN license_program lp 
ON co.license_program_id =
JOIN license_unit lu 
ON cos.license_unit_id =
JOIN customer_unit_subscription cus 
ON cos.license_unit_id = cus.license_unit_id 
AND cus.customer_id =
WHERE cos.customer_order_id = customer_order_id;

Author: Alex T. Silverstein

A developer who wants to help you and himself by writing down good tidbits for free

Leave a Reply

Your email address will not be published. Required fields are marked *