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.

Patrick Hofman · Accepted Answer · 2014-06-10 12:07:54Z

5

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

answered Jun 10, 2014 at 12:07

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SuN Over a year ago

thanks a lot for your quick answer, it works like a charm! I also tried something with coalesce before, but forgot the OUTER JOIN, it makes sense now.

NaughtySquid Over a year ago

Just came here to say thanks, found this after googling and was exactly what I needed to change the data based on an ID :)

paxdiablo · Accepted Answer · 2014-06-10 12:13:32Z

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.

Collectives™ on Stack Overflow

MySQL join a different table based on field value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related