Multiple joins into one column

1
votes

I have query that LEFT joins information from two tables. With code with following joins

LEFT JOIN A on source = news_id 
LEFT JOIN B on source = other_news_id

How can join or display data from the two columns so it produces one column information.

ID  source    Left Join on ID a   Left Join on ID b   
a    1            info1             <null>
a    2            info2             <null>
b    3            <null>            info3
b    4            <null>            info4

Something along the lines of

ID  source   info
a    1       info1             
a    2       info2
b    3       info3             
b    4       info4

How can I bring all left joins into one column?

4
mysql
Are there rows that have both a.id and b.id not null? If yes, the order inside the COALESCE() function matters (the first non-null will be chosen). If no, you may be able to rewrite the query with a UNION of 2 queries. - ypercubeᵀᴹ

4 Answers

4
votes

You can use COALESCE() if there will always only be one value. It returns the first non-null argument.

SELECT ID, source, COALESCE(infoa, infob) AS info FROM ...
2
votes

The COALESCE function will do this.

See the MySQL documentation for examples of how to use this function.

2
votes

From the sample data, it seems that no A.news_id is never equal to a B.other_news_id and the two columns you want to COALESCE have at least one NULL value.

I also guess your FROM clause is something like this:

FROM  T
  LEFT JOIN A on T.source = A.news_id 
  LEFT JOIN B on T.source = B.other_news_id

If that's the case, you could also rewrite the query with a UNION:

SELECT ID, source, infoa AS info
FROM  T
  JOIN A on T.source = A.news_id 
WHERE ...

UNION ALL

SELECT ID, source, infob
FROM  T
  JOIN B on T.source = B.other_news_id
WHERE ...
0
votes

If I understand you correctly, you need to use INNER JOINs instead of LEFT JOINs