Using the assigned name of a field with 'AS' within a JOIN

Question

I have this code..

SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso FROM gimko_v2.users AS u
        INNER JOIN gimko_v2.countries AS c ON u.country=c.id 
        LEFT JOIN gimko_v2.countries AS c2 ON u.state=c2.id 
        LEFT JOIN gimko_blesta.countries AS bc ON c.country_iso_code=bc.alpha2
        LEFT JOIN gimko_blesta.states AS bs ON bc.alpha2=bs.country_alpha2 AND real_state=bs.name
        WHERE u.blesta_id=0;

The problem likes in this area:

COALESCE(c2.name,u.state) AS real_state

and

LEFT JOIN gimko_blesta.states AS bs ON bc.alpha2=bs.country_alpha2 AND real_state=bs.name

I am getting this error:

Error Code: 1054
Unknown column 'real_state' in 'on clause'

How can I use the result of COALESCE(c2.name,u.state) in a JOIN?

Thanks!

Answer 1

Does gimko_blesta.states even need to be in the source tables? None of its columns are in the select list/criteria, and it's joined in with a LEFT JOIN, so doesn't seem like it's necessary.

In any case, you would need to make it a derived table first, and join gimko_blesta.states to the derived table, or just use the expression in the JOIN, like COALESCE(c2.name,u.state) = bs.name, below:

SELECT u.*, COALESCE(c2.name,u.state) AS real_state, c.name AS real_country, bc.alpha3 AS country_iso 
FROM gimko_v2.users AS u
        INNER JOIN gimko_v2.countries AS c ON u.country=c.id 
        LEFT JOIN gimko_v2.countries AS c2 ON u.state=c2.id 
        LEFT JOIN gimko_blesta.countries AS bc ON c.country_iso_code=bc.alpha2
        LEFT JOIN gimko_blesta.states AS bs ON bc.alpha2=bs.country_alpha2 AND COALESCE(c2.name,u.state) = bs.name
        WHERE u.blesta_id=0;