So i have two tables

I was wondering if anyone could help. So in my main table I have strings that have different formats.

Mac address are getting populated as the meter but it they are still linked in another table.

What is the best way to clean this data in bigquery to pull back the desired results of just the meters inplace of the mac addresses?

I have tried this so far but I'm not getting any results back, and I'm still getting mac address back where the meter should be? The thing with the left join table is im also trying to return the MAP ID so long as the meter and L_meter join together too.

SELECT DISTINCT
    COALESCE(table2.L_METER,METER) AS METER,
    MAP.map_id AS Map,

FROM `table1 ` AS A
LEFT JOIN `table2` AS MAP ON METER = L_METER
                            AND METER = MAP.mac_address

Main Table

Left join table

Desired Results

CodePudding user response：

You can use COALOESCE() with left join. If table1.METER=table2.mac_address then L_METER will be selected otherwise L_METER will be NULL and METER will be selected.

Schema and insert statements:

 create table table1 (METER varchar(20));
 insert into table1 values('G4F00113102000');
 insert into table1 values('904DC300000RE122');
 insert into table1 values('904DC3000001E131');
 insert into table1 values('G4F00191402000');
 insert into table1 values('G4F00610542000');
 
 
 create table table2(L_METER    varchar(20),mac_address varchar(20));
 insert into table2 values('E6S15544342061','904DC3000001E131');
 insert into table2 values('G4F00111072000','904DC300000RE122');
 insert into table2 values('G4F00113102000','904DC3000001T133');
 insert into table2 values('G4F00191402000','904DC3000001Y144');
 insert into table2 values('G4F00610542000','904DC3000001U55');

Query:

 select coalesce(table2.L_METER,METER) METER
 from table1 left join table2
 ON table1.METER=table2.mac_address

Output:

db<>fiddle here