pookie pookie - 5 months ago 7
SQL Question

How do I replace a column value, conditionally depending on another column value (with LEFT JOIN)?

I have this query:

SELECT * from #b as t
LEFT outer JOIN WR_16h_vs_MVA_16h_csv as csv
on t.PROBE_ID = csv.PROBE_ID;


which returns results that look like this:

|id|...|...|...|functionCC_A|...|functionCC_B|...|
------------------------------------------
|1 | | | | lalala | | NULL | |
|2 | | | | asdad | | bababa | |
|3 | | | | NULL | | NULL | |
|n | | | | werwer | | NULL | |


There are two
functionCC
columns because of a JOIN. I want a single
functionCC
column but here are the cases:


  1. if
    functionCC_A
    is
    NULL
    , use value from
    functionCC_B

  2. if
    functionCC_A
    has a value and so does
    functionCC_B
    , use
    functionCC_B

  3. if
    functionCC_A
    has a value but
    functionCC_B
    is
    NULL
    , use
    functionCC_A

  4. if both
    NULL
    leave as
    NULL



How can I craft my query so that I can replace the first
functionCC
(
functionCC_A
) column value conditionally, depending on the value in the second
functionCC
column (
functionCC_B
)?

Answer

The COALESCE() function returns the first non-null value from a list so something like:

SELECT COALESCE(csv.functionCC,t.functionCC) AS functionCC 
from #b as t
LEFT outer JOIN WR_16h_vs_MVA_16h_csv as csv
on t.PROBE_ID = csv.PROBE_ID;

This satisfies all the criteria listed since you want 'b' if it's populated and COALESCE() will return NULL if none of the listed fields are populated.

I wasn't sure if the _A and _B were for illustration purposes, assumed one column named functionCC coming from each of the tables, so might have to adjust the names above.

Comments