Question 21670 in Mysql Question posted in
The official MySQL documentation can be found here.

Get non-null columns in a SQL join – Mysql

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

Tags:

2

Answers


  1. 0

    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...
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search