select CASE statement based on two tables

Question

MS SQL Server 2008R2 Management Studio

I am running a SELECT on two tables. I'll simplify it to the part where I'm having trouble. I need to modify the SELECT results to a certain format for a data import. My CASE statement works fine until I get to the point that I need to base the WHEN ... THEN... on a different table column

TABLE1
-----------------
name   |  tag   | code
-----------------------
name1  |   N    | 100
name2  |   N    | 100
name3  |   N    | 200
name4  |   Y    | 100
name5  |   N    | 400
name6  |   N    | 700

CODES
-------------------------
code |   desc
-------------------------
100 | string1
200 | string2
300 | string2
400 | string2
700 | string2

SELECT name,
Case CODES.desc
when 'string1' then 'String 1'
when 'string2' then 'String 2'
when 'string3' then 'String 3'
when 'string4' then 'String 4'
END as description
FROM TABLE1
join CODES on TABLE1.code = CODES.code

This works fine. The problem is if TABLE1.tag = Y, then description needs to be 'Other string' which is not in the CODES table

I tried adding:

Case CODES.desc
.....
when TABLE1.tag = Y then CODES.desc 'Other String'

but it didn't work.

Solution 1

You were close but I think this is what you are looking for. The fact that they are in different tables really doesn't matter to the CASE, just the JOIN:

SELECT name,
Case WHEN Table1.tag = 'Y'
 then CODES.Desc
 ELSE 'Other String'
 END as description
FROM TABLE1
join CODES on TABLE1.code = CODES.code

Solution 2

I can't comment on a post yet. But why have a code table with a code description if you're going to change the description anyways? Instead, you should just modify the current description in that table or add a column with the secondary description you need. Then the case statement is a lot less complex.

    CASE WHEN TABLE1.tag = 'Y'
    THEN 'Other String'
    ELSE CODES.other_desc
    END AS description

Solution 3

You can use the slightly more robust case syntax and express the cases as conditions instead of just possible values:

SELECT name,
       CASE WHEN table1.tag = 'Y' THEN 'other string'
            WHEN codes.[desc] = 'string1' THEN 'String 1'
            WHEN codes.[desc] = 'string2' THEN 'String 2'
            WHEN codes.[desc] = 'string3' THEN 'String 3'
            WHEN codes.[desc] = 'string4' THEN 'String 4'
       END AS description
FROM   table1
JOIN   codes ON table1.code = codes.code

Comments

[Marc B]

you can nest case statements. it's ugly, but doable. basically the equivalent of nested if(if())

[clemens321]

case when table1.tag = 'Y' then 'Other string' else (case codes.desc when 'string1' then 'String 1' when ... end) end - don't know if you need the brackets, but at least it helps reading

[CactusCake]

You don't need the brackets, but they also don't do any harm.

[Minott Opdyke]

That would make sense but that part is out of my control

[Minott Opdyke]

This answer and the one from Steve are similar and this format worked for me. Thanks