Multiple tables join and applying case when

Question

I am trying to use case when phrase to change the result value while joining multiple tables.

SELECT CASE WHEN CENSUS = '0' THEN 'SP_CENSUS0' END AS SP_RESULT FROM SP
SELECT CASE WHEN CENSUS = '0' THEN 'SR_CENSUS0' END AS SR_RESULT FROM SR

    SP             SR          TARGET
   ------         ------      --------
   CODE           CODE        CODE
   CENSUS         CENSUS

and I wish to join the three tables TARGET, SP and SR while SP AND SR should do 1:1 mapping and hope to use column CODE to be used for join. Each CENSUS colulmn may/may not have different results.

I want something like this to be in my result

      SP_RESULT            SR_RESULT
-------------------------------------
 1   (0)SP_CENSUS0      (0)SR_CENSUS0
 2         1                   4
 3         2            (0)SR_CENSUS0
 4         3                   1
 5   (0)SP_CENSUS0             5

my results may have 0,1,2,3,4,5 and I want only the result 0 to be changed and these two tables are joined

Answer 1

What is the point of TARGET if SR and SP are 1:1 via CODE field?

You can use table.field in your case statements:

select case when SP.CENSUS = '0' then 'SP_CENSUS0'
    else SP.CENSUS
    end as SP_RESULT,

    case when SR.CENSUS = '0' then 'SR_CENSUS0'
    else SR.CENSUS 
    end as SR_RESULT
FROM SR JOIN SP ON SR.CODE = SP.CODE

Also: watch out for data types, if Census column is number, you will have to CAST to VARCHAR in the ELSE assignment.