在sql中使用来自多个表的外键 [英] Use foreign key from more than one table in sql
问题描述
我的sql数据库中有一个表（MainTable），它有5个字段。其中一个字段（FID）是同一数据库中另外5个表的外键。其他字段（TypeID）包含一个显示的ID FID中的密钥属于该5个表中的哪个表。例如，如果FID = 1000且TypeID = 1，则MainTable中的记录表示1000是表1中的主键。
现在我想将这些表连接到一个新表，这样我就可以在这5个表和MainTable之间创建一个视图并使用LEFT OUTER JOIN命令：
< pre lang =SQL> 选择 * 从
（选择 * 从
（选择 * 来自
（选择 * 来自
（ S.选择 * 从 MainTable LEFT OUTER JOIN 表1 ON MainTable.FID = Table1.ID AND MainTable.TypeID = 1） AS Temp1
LEFT OUTER JOIN 表2 ON Temp1.FID = Table2.ID < span class =code-keyword> AND Temp1.TypeID = 2） AS Temp2
LEFT OUTER JOIN 表3 ON Temp2.FID = Table3.ID AND Temp2.TypeID = 3） AS Temp3
< span class =code-keyword> LEFT OUTER JOIN 表4 ON Temp3.FID = Table4.ID AND Temp3.TypeID = 4） AS Temp4
LEFT OUTER JOIN 表5 ON Temp4.FID = Table5.ID AND Temp4.TypeID = 5
1）以上查询是这个目标最好的一个？
2）有没有办法在特殊列中合并结果表的某些列？我的意思是如果结果是这样的这个：
field1 field2 field3 field4
-------------------- -----------------------------
value1 NULL NULL NULL
NULL value2 NULL NULL
NULL NULL value3 NULL
NU LL NULL NULL值4
将其转换为如下所示：
new_field
-------------
value1
value2
value3
value4
好的 - 我不是那个投票给你问题的人，但我能理解为什么有人这么做。
回答你的问题
1）否 - 对于你的目标，这不是最好的查询。我不相信你的MainTable设计是最好的，但至少我认为它是可扩展的。不需要所有临时表和多个选择......这些在SQL2005上甚至不能按原样运行。这个简化版本也可以正常工作
选择 * 从 MainTable
LEFT OUTER JOIN table1 MainTable.FID = table1.ID 和 MainTable.TypeID = 1
LEFT OUTER > JOIN table2 MainTable.FID = table2.ID 和 MainTable .TypeID = 2
LEFT OUTER JOIN table3 MainTable.FID = table3.ID 和 MainTable.TypeID = 3
LEFT OUTER JOIN table4 on MainTable.FID = table4.ID 和 MainTable.TypeID = < span class =code-digit> 4
LEFT OUTER JOIN table5 MainTable.FID = table5.ID 和 MainTable.TypeID = 5
2）要合并列，请使用COALESCE表达式合并的有用链接 [ ^ ]例如如果你的每个tablex上有一列数据
选择 MainTable.ID，MainTable.FID，MainTable.TypeID，
COALESCE （table1.data，table2.data，table3.data，table4 .data，table5.data）
来自 MainTable
LEFT OUTER JOIN table1 on MainTable.FID = table1.ID < span class =code-keyword>和 MainTable.TypeID = 1
LEFT OUTER JOIN table2 MainTable。 FID = table2.ID 和 MainTable.TypeID = 2
LEFT OUTER JOIN table3 MainTable.FID = table3.ID 和 MainTable.TypeID = 3
LEFT OUTER JOIN table4 MainTable.FID = table4.ID 和 MainTable.TypeID = 4
LEFT OUTER JOIN table5 MainTable.FID = table5.ID 和 MainTable.TypeID = 5
返回类似的内容...
ID FID TypeID（无列名）
1 1 1 table1-1
2 2 1 table1-2
3 3 1 table1-3
4 1 2 table2-1
5 2 2 table2-2
6 3 2 table2-3
7 1 3 table3-1
8 2 3 table3-2
9 3 3 table3-3
10 1 4 table4-1
11 2 4 table4-2
12 3 4 table4-3
13 1 5 table5-1
14 2 5 table5-2
15 3 5 table5-3
I have a table(MainTable) in my sql database which has 5 field.One of these fields(FID) is foreign key of 5 other tables in the same database.The other field(TypeID) is containing an ID which shows that the key in FID is belong to which table of that 5 tables.For example if FID=1000 and TypeID=1 for a record in MainTable it means that 1000 is a primary key in table number one.
Now i want to join these tables to a new table so i have made a view and used LEFT OUTER JOIN command between each of those 5 tables and MainTable :
Select * From
(Select * From
(Select * From
(Select * From
(Select * From MainTable LEFT OUTER JOIN Table1 ON MainTable.FID=Table1.ID AND MainTable.TypeID=1)AS Temp1
LEFT OUTER JOIN Table2 ON Temp1.FID=Table2.ID AND Temp1.TypeID=2)AS Temp2
LEFT OUTER JOIN Table3 ON Temp2.FID=Table3.ID AND Temp2.TypeID=3)AS Temp3
LEFT OUTER JOIN Table4 ON Temp3.FID=Table4.ID AND Temp3.TypeID=4)AS Temp4
LEFT OUTER JOIN Table5 ON Temp4.FID=Table5.ID AND Temp4.TypeID=5
1)Is the above query the best one for this aim?
2)Is there any way to merge some columns of the result table in a special column?I mean that if the result is something like this :
field1 field2 field3 field4
-------------------------------------------------
value1 NULL NULL NULL
NULL value2 NULL NULL
NULL NULL value3 NULL
NULL NULL NULL value4
convert it to something like below :
new_field
-------------
value1
value2
value3
value4
Ok - I wasn''t the one that voted your question down but I can understand why someone did.
To answer your questions
1) No - this is not the best query for whatever your aim is. I''m not convinced that your design for MainTable is the best either but at least it''s extensible I suppose. There is no need for all of the temporary tables and multiple selects ... which wouldn''t even run as-is on SQL2005. This simplified version would work just as well
select * from MainTable LEFT OUTER JOIN table1 on MainTable.FID=table1.ID and MainTable.TypeID = 1 LEFT OUTER JOIN table2 on MainTable.FID=table2.ID and MainTable.TypeID = 2 LEFT OUTER JOIN table3 on MainTable.FID=table3.ID and MainTable.TypeID = 3 LEFT OUTER JOIN table4 on MainTable.FID=table4.ID and MainTable.TypeID = 4 LEFT OUTER JOIN table5 on MainTable.FID=table5.ID and MainTable.TypeID = 5
2) To merge columns use the COALESCE expression Useful link on coalesce[^] e.g. if you have a columndataon each of your tablex
select MainTable.ID, MainTable.FID, MainTable.TypeID, COALESCE(table1.data, table2.data, table3.data, table4.data, table5.data) from MainTable LEFT OUTER JOIN table1 on MainTable.FID=table1.ID and MainTable.TypeID = 1 LEFT OUTER JOIN table2 on MainTable.FID=table2.ID and MainTable.TypeID = 2 LEFT OUTER JOIN table3 on MainTable.FID=table3.ID and MainTable.TypeID = 3 LEFT OUTER JOIN table4 on MainTable.FID=table4.ID and MainTable.TypeID = 4 LEFT OUTER JOIN table5 on MainTable.FID=table5.ID and MainTable.TypeID = 5
returns something like ...
ID FID TypeID (No column name) 1 1 1 table1-1 2 2 1 table1-2 3 3 1 table1-3 4 1 2 table2-1 5 2 2 table2-2 6 3 2 table2-3 7 1 3 table3-1 8 2 3 table3-2 9 3 3 table3-3 10 1 4 table4-1 11 2 4 table4-2 12 3 4 table4-3 13 1 5 table5-1 14 2 5 table5-2 15 3 5 table5-3
这篇关于在sql中使用来自多个表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
