MySQL - Using column value for joining in the same query

Question

I have three tables that looks something like this:

Table joins

|ID|JOIN_NAME|
 1  persons
 2  companies

Table information

|ID|JOIN_ID|
 1  1
 2  2

Table information_extra_persons

|ID|INFORMATION_ID|NAME|
 1  1              John

Table information_extra_companies

|ID|INFORMATION_ID|NAME|
 1  2              IBM

How can i join together these tables in one SQL? I've tried something like:

SELECT * FROM `information`
INNER JOIN `information_extra_(SELECT `name` FROM `joins` WHERE `id` = `join_id`)`
ON `information_extra_(SELECT `name` FROM `joins` WHERE `id` = `join_id`)`.`information_id` = `information`.`id`

but I can't get it to work. Of course this isn't my actual table setup, but it's the same principle. Does anyone know how to get all the info in just one SQL?

if you need to have the JOIN dynamic this is impossible as SQL doesn't provide anything to do that! — Yahia
– Yahia, Commented Oct 31, 2011 at 9:52

score 3 · Accepted Answer · 2011-10-31 10:16:48Z

That's actually four tables, not three. This isn't just a nitpick - it looks as though the substance of your question is "how can I use the name of the table as part of the join criteria?" (ie. how can the information_extra_ tables be treated as a single table?)

To which the answer is: you can't. (Outside of dynamic SQL.)

In this specific case, the following should return what I think you are looking for:

select j.join_name joined_entity,
       case when j.join_name = 'persons' then p.name
            else c.name
       end joined_entity_name
from information i
inner join joins j on i.join_id = j.id
left join information_extra_persons p on i.id = p.information_id
left join information_extra_companies c on i.id = c.information_id

Alternatively, a less efficient (but more general) approach might be:

select j.join_name joined_entity,
       v.name joined_entity_name
from information i
inner join joins j on i.join_id = j.id
inner join (select 'persons' entity, information_id, name from information_extra_persons
            union all
            select 'companies' entity, information_id, name from information_extra_companies) v
           on i.id = v.information_id and j.join_name = v.entity

Collectives™ on Stack Overflow

MySQL - Using column value for joining in the same query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related