panxpress panxpress - 3 months ago 9
MySQL Question

Do Multiple Left Join on condition and get non null column value

I have a table containing the last comments posted on the website, and I'd like to join a different table depending on the comment type.

Comments Table is similar to this structure:

id | type | ressource_id |
---+------+--------------+
1 | 1 | 10 |
2 | 3 | 7 |
3 | 3 | 12 |
4 | 1 | 22 |
5 | 4 | 22 |
6 | 5 | 23 |


News Table:

news_id | notes| date |
--------+------+--------------+
10 | | 2015-08-12 |
22 | | 2015-07-12 |


Tutorial Table:

tuto_id | notes| date |
--------+------+--------------+
7 | | 2015-06-15 |
12 | | 2015-05-14 |


... Similar table for type = 4, 5, 6

Now in order to get specific comments I am doing a left join on the two tables.

SELECT co.*
FROM Comments co
LEFT JOIN News n
ON co.id = n.news_id AND co.type = 1
LEFT JOIN Tutorial t
ON co.id = t.tuto_id AND co.type = 3
WHERE (co.type IN (1,3))


I am interested in getting the date from the left table. How can I include that column in output list.

Result desired: (date from joining table)

id | type | ressource_id | date |
---+------+--------------+--------------+
1 | 1 | 10 | 2015-08-12 |
2 | 3 | 7 | 2015-06-15 |
3 | 3 | 12 | 2015-05-14 |
4 | 1 | 22 | 2015-07-12 |


Thanks.

Answer

Since you will never get a date from News and Tutorial for the same comment you might go withCOALESCE`:

SELECT co.*, COALESCE(n.date,t.date)
  FROM Comments co
       LEFT JOIN News n 
              ON co.id = n.news_id AND co.type = 1
       LEFT JOIN Tutorial t
              ON co.id = t.tuto_id AND co.type = 3
 WHERE (co.type IN (1,3))

COALESCE will return the first argument that is not null, so if there is a matching news it will return the date from News and if there is no matching news but a matching tutorial it will return the date from Tutorial.

Comments