MYSQL join tables based on column data and table n

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

Only with dynamic SQL. It is also possible to left join many different tables and use CASE based on type, but the tables would be all have to be known in advance.

It would be easier to recommend an appropriate design if we knew more about what you are trying to achieve, what your design currently looks like and why you've chosen that particular table design in the first place.

Say you have a table of foods:

id INT
foodtype VARCHAR(50) (right now it just contains 'hotdog' or 'hamburger')
name VARCHAR(50)

Then hotdogs:

id INT
length INT
width INT

Then hamburgers:

id INT
radius INT
thickness INT

Normally I would recommend some system for constraining only one auxiliary table to exist, but for simplicity, I'm leaving that out.

SELECT f.*, hd.length, hd.width, hb.radius, hb.thickness
FROM foods f
LEFT JOIN hotdogs hd
    ON hd.id = f.id
    AND f.foodtype = 'hotdog'
LEFT JOIN hamburgers hb
    ON hb.id = f.id
    AND f.foodtype = 'hamburger'

Now you will see that such a thing can be code generated (or even for a very slow prototype dynamic SQL on the fly) from SELECT DISTINCT foodtype FROM foods given certain assumptions about table names and access to the table metadata.

The problem is that ultimately whoever consumes the result of this query will have to be aware of new columns showing up whenever a new table is added.

So the question moves back to your client/consumer of the data - how is it going to handle the different types? And what does it mean for different types to be in the same set? And if it needs to be aware of the different types, what's the drawback of just writing different queries for each type or changing a manual query when new types are added given the relative impact of such a change anyway?

Answer 2

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.