I have a query that gets the data from two tables (TABLE_ONE left join TABLE_TWO) that have the same columns. I need to get the data from TABLE_ONE, but if the data is null and in TABLE_TWO it is not null, then the column of TABLE_TWO is returned.
TABLE_ONE
data1 | data2 | data3 | data4 |
---|---|---|---|
car | red | 4 | |
bike | blue |
TABLE_TWO
data1 | data2 | data3 | data4 |
---|---|---|---|
car | spain | 7 | |
bike | blue | 9 |
This is the query:
select distinct *
from TABLE_ONE T1
left join TABLE_TWO T2 on T1.data1 = T2.data1
and (T1.data2 = T2.data2 OR (T1.data2 like '' OR T2.data2 like ''))
and (T1.data3 = T2.data3 OR (T1.data3 like '' OR T2.data3 like ''))
This is what I get:
data1 | data2 | data3 | data4 | data1 | data2 | data3 | data4 |
---|---|---|---|---|---|---|---|
car | red | 4 | car | spain | red | 7 | |
bike | blue | bike | blue | 9 |
This is what I need:
data1 | data2 | data3 | data4 |
---|---|---|---|
car | spain | red | 4 |
bike | blue | 9 |
Is it possible to merge the columns that have the same name?, that is, a different column, but that are not null, if both columns are null, take the one from TABLE_ONE
CodePudding user response:
Use coalesce
Select
coalesce(t1.data1, t2.data1) Data1,
coalesce(t1.data2, t2.data2) Data2,
coalesce(t1.data3, t2.data3) Data3,
coalesce(t1.data4, t2.data4) Data4
From...
CodePudding user response:
You need a full outer join here. MySQL does not directly support them, but we can emulate them via a union query:
SELECT t1.data1,
COALESCE(t1.data2, t2.data2) AS data2,
COALESCE(t1.data3, t2.data3) AS data3,
COALESCE(t1.data4, t2.data4) AS data4
FROM TABLE_ONE t1
LEFT JOIN TABLE_TWO t2 ON t2.data1 = t1.data1
UNION ALL
SELECT t2.data1, t2.data2, t2.data3, t2.data4
FROM TABLE_ONE t1
RIGHT JOIN TABLE_TWO t2 ON t2.data1 = t1.data1
WHERE t1.data1 IS NULL;