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:

SELECT    
    (        # The old-style join syntax used here is REQUIRED for the correlated subquery:
            SELECT COUNT(DISTINCT uus.id) as uus_count
            FROM user_unit_subscription uus, sims_organization so, user u, sims_user su         
            WHERE so.id = c.sims_organization_id
            AND uus.license_unit_id = lu.id
            AND u.id = uus.user_id
            AND su.id = u.sims_id
            AND su.enable = 1
            AND so.deleted = 0
            AND su.organization_id = so.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 = co.id
JOIN customer c 
ON co.customer_id = c.id
JOIN license_program lp 
ON co.license_program_id = lp.id
JOIN license_unit lu 
ON cos.license_unit_id = lu.id
JOIN customer_unit_subscription cus 
ON cos.license_unit_id = cus.license_unit_id 
AND cus.customer_id = c.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 *