如何使用 ID 连接多个 SQL 表?

2021-11-20 00:00:00 join sql mysql

我有 4 个不同的表要加入.表格的结构如下:

I have 4 different tables that I want to join. The tables are structured with columns as follows:

TableA - aID | nameA | dID

TableB - bID | nameB | cID | aID

TableC - cID | nameC | date

TableD - dID | nameD

从表 A 开始，我了解如何使用 b 连接表 a 和 c，因为 b 具有这些表的主键.我也希望能够在 TableA 上加入表 TableD.下面是我的 SQL 语句，它首先连接表 A 和 B，然后将其连接到 C:

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:

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()))

当我尝试添加另一个连接以包含 D 时，出现TableD"未知的错误:

When I attempt to add another join, to include D, I get an error that 'TableD' is unknown:

 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()))

推荐答案

你想要更像这样的东西:

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())

在您的示例中，您实际上并未包含 TableD.您所要做的就是像以前一样执行另一个连接.

In your example, you are not actually including TableD. All you have to do is perform another join just like you have done before.

注意:您会注意到我删除了您的许多括号，因为在您拥有它们的大多数情况下它们确实不是必需的，并且只会在尝试阅读代码时增加混乱.正确的嵌套是使代码可读和分离的最佳方式.

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.

相关文章