如何将两个表的两列插入另一表的一列中

Question

嗨，我在加入3个表

时遇到了麻烦

table1(id1,name_1)
Table2(id2,top,name_2,fk_id1)
table3(id3,top,name_3,fk_id1)

，因此我希望o有：

tableResult(id1,name_1,top,name_2,name_3)

ps：top包含来自table2.top和table3.top

Answer 1

如果您只想加入，以下查询应该这样做。

SELECT *
FROM table1 a
JOIN table2 b ON b.fk_id1 = a.id1
JOIN table3 c ON c.fk_id1 = a.id1

Answer 2

这是我假设您的结果需要的样子：

1. ID1, Name1, Top (from table 2), Name2, Name3
2. ID1, Name1, Top (from table 3), Name2, Name3

例如：

1. 514, Bill, 2, Sam, Jill
2. 514, Bill, 3, Sam, Jill
3. 515, Jack, 5, Bob, Tom
4. 515, Jack, 6, Bom, Tom

我认为您需要使用UNION ALL操作员。

SELECT A.ID_1, A.Name_1, B.Top, B.Name_2, C.Name_3
FROM Table1 AS A
LEFT OUTER JOIN Table2 AS B
ON A.Id_1 = B.Id_1
     LEFT OUTER JOIN Table3 AS C
     ON A.ID_1 = C.ID_1
UNION ALL
SELECT A.ID_1, A.Name_1, C.Top, B.Name_2, C.Name_3 --Notice that this grabs Top from Table 3
FROM Table1 AS A
LEFT OUTER JOIN Table2 AS B
ON A.Id_1 = B.Id_1
     LEFT OUTER JOIN Table3 AS C
     ON A.ID_1 = C.ID_1

我不确定您的规则，因此您可能需要使用与左外的Join不同的加入。