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

All we need is an easy explanation of the problem, so here it is.

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,

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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`)

Method 2

Finally I found the answer:

SELECT 
`user email`,
`user join date`,
location,
iso_country,
iso_country_id,
users.country,
users.event_type,
desirecountry,

CASE 
    WHEN EXISTS (SELECT TOP 1 * FROM iso_country WHERE country_name = desirecountry) 
    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`)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply