When a column has a certain string, i want to replace it with a string from my left join-CodePudding

I was wondering if anyone could help. I want my main table to keep the meter records in my main table and when i get 'Others' i want these to be replaced by the left join tables.

The issue is when im doing my case when, my result is coming back as null? The thing is with the data im left joining too it has a lot of other data that i have to filter and a join on with which might make this question a bit more complicated. First of all i just want to make sure my case when is working correctly before i do anymore testing.

Thanks!

WITH CTE_MAIN AS (

SELECT DISTINCT

    EX.Meter,
    CASE WHEN (EX.Metermodel) = 'Other'
        THEN OJ.Metermodel
        ELSE EX.Metermodel
        END AS MeterModelRemoved

FROM `MAINTABLE` AS EX 
LEFT JOIN `LEFTTABLE` AS OJ ON EX.METER=OJ.METER)

SELECT * #Test 
FROM CTE_MAIN
WHERE Meter = 'G4A50027940101'

Main Table

METER	Metermodel
G4A50027940101	Other
000807612	Other
L0117797762M	Other
009X091	U6
5046314S	G4

Left join table

METER	Metermodel
G4A50027940101	U6
000807612	G4
L0117797762M	G4
009X091	U6
5046314S	LPG

Result: MeterModelRemoved = null

CodePudding user response：

Few more options:

In case if both tables have exact same structure (as it is in your question) - consider below approach

select if(t1.Metermodel = 'Other', t2, t1).*
from MainTable t1
left join LeftTable t2
using(METER)

in case if structure of second table is different and potentially has more columns that you don't need in output - use classic approach

select METER, if(t1.Metermodel = 'Other', t2.Metermodel, t1.Metermodel) as Metermodel
from MainTable t1
left join LeftTable t2
using(METER)

if applied to sample data in y our question - both have same output

CodePudding user response：

The only time you will have null value is if there is no matching record in the second table. If you want to handle this, you can use the value from the main table as a fallback, which can be done using COALESCE.

CASE WHEN (EX.Metermodel) = 'Other'
    THEN COALESCE(OJ.Metermodel, EX.Metermodel)
    ELSE EX.Metermodel
    END AS MeterModelRemoved