基于列数据和表名的MYSQL连接表 [英] MYSQL join tables based on column data and table name
问题描述
我想知道这是否可能.
我想基于表1的数据联接2个表. 示例表1中有列food，其数据为"hotdog".
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.
是否可以像这样进行JOIN操作.
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?.
谢谢.
推荐答案
不，您不能连接到table1中每行的不同表，甚至不能使用@Cade Roux建议的动态SQL.
No, you can't join to a different table per row in table1, not even with dynamic SQL as @Cade Roux suggests.
对于食物为热狗"的行，您可以联接到hotdog表，对于食物的其他特定值，可以联接至其他表.
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.
您正在做的事情称为多态关联.即，table1中的外键取决于table1另一列中的值，引用多个父"表中的行.这是关系数据库程序员的常见设计错误.
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?
我还在演示文稿中介绍了多态关联的解决方案实用SQL中的面向对象模型，以及在我的书中 SQL反模式:避免数据库的陷阱编程.
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.
这篇关于基于列数据和表名的MYSQL连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
