CODEWITHSUNDEEP
mysql
rodling
rodling

Reputation: 998

Multiple joins into one column

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?

Upvotes: 1

Views: 90

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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 ...

Upvotes: 2

G-Nugget
G-Nugget

Reputation: 8836

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 ...

Upvotes: 4

Ken Keenan
Ken Keenan

Reputation: 10538

The COALESCE function will do this.

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

Upvotes: 2

Melanie
Melanie

Reputation: 3111

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

Upvotes: 0

Related Questions