如何使用ID联接多个SQL表? [英] How can I join multiple SQL tables using the IDs?

问题描述

我要加入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.

这篇关于如何使用ID联接多个SQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！