MySQL join a different table based on field value

Question

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      |

What I'd like to do is to join the "News" table if type type = 1 (on news.id = comments.ressource_id), "tutorial" table if type type = 3, etc.

How can I do this please? I've tried different queries using CASE and UNION, but never got the expected results.

Thanks.

Answer 1

Try using left outer join and a on clause matching on the type:

select coalesce(n.id, t.id) id
,      c.type
,      c.resource_id
from   comments c
left
outer
join   news n
on     n.id = comments.resource_id
and    c.type = 1
left
outer
join   tutorial t
on     t.id = comments.resource_id
and    c.type = 3

Answer 2

You can do this with unioned queries assuming that you can coerce partial queries into producing a similar schema, along the lines of:

select n.id as id, c.something as something
    from news n, comments c
    where n.type = 1
    and   n.id = c.resource_id
union all
select n.id as id, t.something as something
    from news n, tutorial t
    where n.type = 3
    and   n.id = t.resource_id

In other words, the first query simply joins news and comments for rows where news.type indicates a comment, and the second query joins news and tutorials for rows where news.type indicates a tutorial.

Then the union combined the two into a single record set.

I'd steer clear of case in this situation (and many others) since it almost always invariably requires per-row modification of the data, which rarely scales wee. Running two queries and combining the results is usually more efficient.