Case When Statement - Three columns

Advanced case when statement in T-SQL using three columns

Hope someone can help with the following :)

I have two tables in SQL.

Table 1 has 4 columns - person_id, A, B, and C.
Table 2 has 3 look-up columns - A, B, and C

I want to carry out the following:

Look down Table 1, column A, find value in Table 2, column A
If no value, go to Table 1, column B.
Look down Table 1, column B, find value in Table 2, column B
If no value, go to Table 1, column C
Look down Table 1, column C, find value in Table 2, column C
If no values in any column put 'Null'

So I know how to write a simple case when statement. However, I think that 'case when' only works on one column. I've started to write out the code but need help to get it right.

Select person_id, 
case when 1.A is not null then 2.A
when 1.B is not null then 2.B
else 1.C
end as CODE
from table 1
left join table 2
Order by person_id

Would appreciate any help you can give, thank you.

Solution

Your sql seems mostly correct, except for the else 1.C.

Since a CASE WHEN will return 1 value, depending on the first positive WHEN criteria.

And a CASE returns NULL as default.
So ELSE null isn't really needed.

The table descriptions do indicate that Table2 only contains 1 row.

If so, then you can cross join them.

SELECT t1.person_id, 
CASE 
WHEN t1.A IS NOT NULL THEN t2.A
WHEN t1.B IS NOT NULL THEN t2.B
WHEN t1.C IS NOT NULL THEN t2.C
END AS [CODE]
FROM [Table 1] t1
CROSS JOIN (
   SELECT TOP 1 A, B, C
   FROM [Table 2]
   ORDER BY A DESC, B DESC, C DESC
) t2