Mysql – How to use SQL CASE result to compare a column and get another case result

MySQLredshift

I'm trying to compare my case result (desirecountry) with an existing column (so.country_name) to get another case result (Final_Country_Map_Name):

     SELECT subs."user email", subs."user join date", subs.location,
                iso.country_name as iso_country, iso.country_id as iso_country_id, 
                    users.country, users.event_type,
    -- 1.  
    CASE  WHEN subs.location = iso.country_name THEN iso.country_name
             ELSE users.country END AS **desirecountry**,
    -- 2.
    CASE  WHEN **desirecountry** = iso.country_name THEN **desirecountry**
            --ELSE Null END AS **Final_Country_Map_Name**
          
    FROM subs
    LEFT JOIN
    iso
    ON
    subs.location = iso.country_name
    LEFT JOIN
    users
    ON
    users.user_id = subs."user email"

When I'm using the second CASE conditions the logic is failing, it's not giving the expected output, it's giving me a null value in "final_country_map_name" column instead of "desirecountry" value = United States. Any small help would be helpful, Thank you.
Ex: Column names are:->

location | iso_country  |   country     | desirecountry  | final_country_map_name   &&& Column o/p are: -> 
U.s,      null,          United States,   United States,       null,

Best Answer

Your code looks more like postgre, than MySQL

Also for your information, you should not use spaces in column names and leave the desired output to the gui, that save a lot of time

I used the inner SubSELECT to gather the data and put the second CASE in the outer SELECT

SELECT 
    `user email`,
    `user join date`,
    location,
    iso_country,
    iso_country_id,
    users.country,
    users.event_type,
    desirecountry,
    CASE
        WHEN desirecountry = iso_country THEN desirecountry
        ELSE NULL
    END AS Final_Country_Map_Name
FROM
    (SELECT 
        subs.`user email`,
            subs.`user join date`,
            subs.location,
            iso.country_name AS iso_country,
            iso.country_id AS iso_country_id,
            users.country,
            users.event_type,
            CASE
                WHEN subs.location = iso.country_name THEN iso.country_name
                ELSE users.country
            END AS desirecountry
    FROM
        subs
    LEFT JOIN iso ON subs.location = iso.country_name
    LEFT JOIN users ON users.user_id = subs.`user email`)
Related Question