[SQL CASE WHEN]Why case when is not working when I specify two conditions at the same time?

Hi I was trying to a different version of SQL query that I was given from datacamp.

The query I was provided with is

SELECT 
    date,
    -- Identify the home team as Barcelona or Real Madrid
    case when hometeam_id = 8634 then 'FC Barcelona' 
        else 'Real Madrid CF' end as home,
    -- Identify the away team as Barcelona or Real Madrid
    case when awayteam_id = 8634 then 'FC Barcelona' 
        else 'Real Madrid CF' end as away
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
      AND (awayteam_id = 8633 OR hometeam_id = 8633);

I tried to tweak it
thinking, “instead of setting one condition for each case scenario, let’s set two conditions at the same time by connecting the two conditions with ‘and ”
My query is as below.

select date,
case when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
     when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
     end as home,
case when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
     when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
     end as away
from matches_spain
where (hometeam_id=8634 and awayteam_id=8633)
or (hometeam_id=8633 and awayteam_id=8634)

teams_spain table is like this

and

matches_spain table is like this

“matches_spain” is a record of soccer games during some season with home team ids and away team ids. Each team has its own unique id and the unique ids can be found in teams_spain table.

Why does my alternative code print wrong output?

desirable output is my wrong output is

Answer

Beside the fact I don’t understand why you want to change the logic like you did, you are using the identic CASE WHEN construct for both home team and away team, so of course, you will get the identic outcome for them. The second CASE WHEN is incorrect. You wrote:

case when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
 when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
 end as away

…this should be vice versa:

case when hometeam_id=8634 and awayteam_id = 8633 then 'Real Madrid CF'
 when hometeam_id=8633 and awayteam_id = 8634 then 'FC Barcelona'
 end as away