CODEWITHSUNDEEP
mysqljoinsubquery
Mike
Mike

Reputation: 24393

When a subquery uses a value from parent query in ON conditions it produces 'Unknown column' error

If I execute the following query, it works fine:

SELECT
CASE WHEN (SELECT COUNT(*)
    FROM rates r
    LEFT JOIN promotions_rates pr
        ON r.id = pr.rate_id
    WHERE pr.promo_id IS NULL
    AND pr.promo_id = promotions.id)
    > 0 THEN 'N' ELSE 'Y' END AS 'all_rates_selected'
FROM promotions

However if I rearrange it to put promotions.id in the ON part instead of WHERE like this:

SELECT
    CASE WHEN (SELECT COUNT(*)
        FROM rates r
        LEFT JOIN promotions_rates pr
            ON r.id = pr.rate_id
            AND pr.promo_id = promotions.id
        WHERE pr.promo_id IS NULL)
        > 0 THEN 'N' ELSE 'Y' END AS 'all_rates_selected'
FROM promotions

I get the error Unknown column 'promotions.id' in 'on clause'. In both cases, I am using promotions.id in the sub-query, but it works in the WHERE but not in the ON. Why would it work with one and not with the other?

Is there another way I can do the second query to count the NULL values?

Upvotes: 0

Views: 127

Answers (1)

kyo
kyo

Reputation: 610

Take a look at this example, maybe it is simpler to understand:

SELECT *
FROM rates r 
LEFT JOIN promotions_rates pr ON r.id=pr.rate_id,
promotions p
WHERE pr.promo_id=p.id

This will success because join condition doesn't include column from promotions. Mysql joins rates and promotions_rates together, creates a temporary table and search with where conditions in the temporary table and promotions.

SELECT *
FROM rates r 
LEFT JOIN promotions_rates pr ON r.id=pr.rate_id AND pr.promo_id=p.id,
promotions p
WHERE pr.promo_id IS NULL

This will fail because when mysql try to join rates and promotions_rates together, it doesn't know anything about promotions. Basically, Your second sample is doing the same thing. If you want it to be success, you should join promotions too.

Like this:

SELECT *
FROM rates r 
LEFT JOIN promotions_rates pr ON r.id=pr.rate_id AND pr.promo_id=p.id
LEFT JOIN promotions p ON pr.promo_id=p.id
WHERE pr.promo_id IS NULL

I haven't run the first query but I think this query will always return 'Y', because in the first query ,you are trying to search for records with id 'null' in the promotions table.

If your 'Y' stands for 'all rates are related to some promotions', You may want to remove the AND pr.promo_id = promotions.id part. If your 'Y' stands for 'all rates are related to this promotion', maybe you can use this query:

SELECT CASE WHEN(p_count.count=r_count.count THEN 'Y' ELSE 'N' END AS all_rates_selected
FROM promotions
LEFT JOIN (
    SELECT count(*) as count,p.id as id
    FROM promotions p
    LEFT JOIN promotions_rates pr ON p.id = pr.promo_id
    LEFT JOIN rates r ON r.id=pr.rate_id
    GROUP BY p.id
) p_count ON promotions.id = p_count.id,
(
    SELECT count(*) as count
    FROM rates
) r_count

I haven't tested this code since I don't have any data. Please let me know if I am wrong, thank you.

Upvotes: 1

Related Questions