MYSQL join tables based on column data and table name

Question

I'm wondering if this its even posible.

I want to join 2 tables based on the data of table 1. Example table 1 has column food with its data beeing "hotdog".

And I have a table called hotdog.

IS it possible to do a JOIN like.

SELECT * FROM table1 t join t.food on id = foodid

I know it doesnt work but, its even posible, is there a work arround?.

Thanks in advance.

Answer 1

No, you can't join to a different table per row in table1, not even with dynamic SQL as @Cade Roux suggests.

You could join to the hotdog table for rows where food is 'hotdog' and join to other tables for other specific values of food.

SELECT * FROM table1 JOIN hotdog ON id = foodid WHERE food = 'hotdog'
UNION
SELECT * FROM table1 JOIN apples ON id = foodid WHERE food = 'apples'
UNION
SELECT * FROM table1 JOIN soups  ON id = foodid WHERE food = 'soup'
UNION 
...

This requires that you know all the distinct values of food, and that all the respective food tables have compatible columns so you can UNION them together.

What you're doing is called polymorphic associations. That is, the foreign key in table1 references rows in multiple "parent" tables, depending on the value in another column of table1. This is a common design mistake of relational database programmers.

For alternative solutions, see my answers to:

• Possible to do a MySQL foreign key to one of two possible tables?
• Why can you not have a foreign key in a polymorphic association?

I also cover solutions for polymorphic associations in my presentation Practical Object Oriented Models In SQL, and in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.