MySQL 如何使用 ID 连接多个 SQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9853586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I join multiple SQL tables using the IDs?
提问by Sun
I have 4 different tables that I want to join. The tables are structured with columns as follows:
我有 4 个不同的表要加入。这些表的结构如下：
TableA - aID | nameA | dID
TableB - bID | nameB | cID | aID
TableC - cID | nameC | date
TableD - dID | nameD
Starting with Table A, I understand how to JOIN tables a and c using b, since b has the Primary Keys for those tables. I want to be able to join table TableD on TableA as well. Below is my SQL statement that first joins tables A and B, then joins that to C:
从表 A 开始，我了解如何使用 b 连接表 a 和 c，因为 b 具有这些表的主键。我也希望能够在 TableA 上加入表 TableD。下面是我的 SQL 语句，它首先连接表 A 和 B，然后将其连接到 C：
SELECT TableA.*, TableB.*, TableC.* FROM (TableB INNER JOIN TableA
ON TableB.aID= TableA.aID)
INNER JOIN TableC ON(TableB.cID= Tablec.cID)
WHERE (DATE(TableC.date)=date(now()))
When I attempt to add another join, to include D, I get an error that 'TableD' is unknown:
当我尝试添加另一个连接以包含 D 时，出现“TableD”未知的错误：
SELECT TableA.*, TableB.*, TableC.*, TableD.* FROM (TableB INNER JOIN TableA
ON TableB.aID= TableA.aID)
INNER JOIN TableC ON(TableB.cID= Tablec.cID)
INNER JOIN TableA ta ON(ta.dID= TableD.dID)
WHERE (DATE(TableC.date)=date(now()))
回答by Justin Pihony
You want something more like this:
你想要更像这样的东西：
SELECT TableA.*, TableB.*, TableC.*, TableD.*
FROM TableA
JOIN TableB
ON TableB.aID = TableA.aID
JOIN TableC
ON TableC.cID = TableB.cID
JOIN TableD
ON TableD.dID = TableA.dID
WHERE DATE(TableC.date)=date(now())
In your example, you are not actually including TableD. All you have to do is perform another join just like you have done before.
在您的示例中，您实际上并未包括TableD. 您所要做的就是像以前一样执行另一个连接。
A note: you will notice that I removed many of your parentheses, as they really are not necessary in most of the cases you had them, and only add confusion when trying to read the code. Proper nesting is the best way to make your code readable and separated out.
注意：您会注意到我删除了您的许多括号，因为在您拥有它们的大多数情况下它们确实不是必需的，并且只会在尝试阅读代码时增加混乱。正确的嵌套是使您的代码可读和分离的最佳方式。
回答by Nemoden
SELECT
a.nameA, /* TableA.nameA */
d.nameD /* TableD.nameD */
FROM TableA a
INNER JOIN TableB b on b.aID = a.aID
INNER JOIN TableC c on c.cID = b.cID
INNER JOIN TableD d on d.dID = a.dID
WHERE DATE(c.`date`) = CURDATE()
回答by Chriseyre2000
You have not joined TableD, merely selected the TableD FIELD (dID) from one of the tables.
您还没有加入 TableD，只是dID从其中一张表中选择了 TableD FIELD ( )。
回答by Manu R S
Simple INNER JOIN VIEW code....
简单的 INNER JOIN VIEW 代码....
CREATE VIEW room_view
AS SELECT a.*,b.*
FROM j4_booking a INNER JOIN j4_scheduling b
on a.room_id = b.room_id;
