Join

擴展查詢以包含附加列

  • August 17, 2018

我有以下查詢,它只給我(除了其他列)方向 ID:

SELECT Users.Name, Users.Surname, Transactions.Timestamp, AccessPoints.Description as Door, Readers.Direction
   FROM (Readers INNER JOIN (AccessPoints INNER JOIN Points_Readers ON AccessPoints.PointID = Points_Readers.PointID)
   ON Readers.ReaderID = Points_Readers.ReaderID) INNER JOIN (Transactions INNER JOIN (AccessNumbers INNER JOIN Users
   ON AccessNumbers.AccessNumberID = Users.AccessNumberID) ON Transactions.Number = AccessNumbers.Number)
   ON Readers.ReaderID = Transactions.ReaderID
   WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#) ORDER BY Transactions.Timestamp;

我想要做的是Directions.DirectionsText通過加入Readers.Direction來擴展查詢以包含DirectionsON Directions.Direction。我在準確放置連接的位置時遇到了麻煩,這樣我就不會在FROM子句中不斷出現錯誤。(我還沒有弄清楚如何讓INNER JOIN使用 Access 的子句需要使用括號進行連接。)

中的列DirectionsDirection(數字)和DirectionText(字元串)

我無法確定連接的確切位置

Directions 連接到 Readers - 因此您必須將其放置在此表旁邊,並將這對放在額外的括號中:

SELECT Users.Name, 
      Users.Surname, 
      Transactions.Timestamp, 
      AccessPoints.Description as Door, 
      Readers.Direction,
      Directions.DirectionsText
FROM ( ( Readers 
        INNER JOIN 
        Directions ON Readers.Direction = Directions.Direction
      )
      INNER JOIN 
      ( AccessPoints 
        INNER JOIN 
        Points_Readers ON AccessPoints.PointID = Points_Readers.PointID
      ) ON Readers.ReaderID = Points_Readers.ReaderID
    ) 
    INNER JOIN 
    ( Transactions 
      INNER JOIN 
      ( AccessNumbers 
        INNER JOIN 
        Users ON AccessNumbers.AccessNumberID = Users.AccessNumberID
      ) ON Transactions.Number = AccessNumbers.Number
    ) ON Readers.ReaderID = Transactions.ReaderID
WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#) 
ORDER BY Transactions.Timestamp;

我不會在 FROM 子句中不斷出錯

使用查詢生成器建構查詢文本 - 如果有任何邏輯問題,它總是會創建正確的程式碼或生成錯誤消息。

或者(如果您想在不使用主程序的情況下“手動”編寫查詢文本)將查詢重寫為笛卡爾形式(使用逗號分隔的表列表和 WHERE 子句中的所有連接條件) - 它不需要括號中的準確性:

SELECT Users.Name, 
      Users.Surname, 
      Transactions.Timestamp, 
      AccessPoints.Description as Door, 
      Readers.Direction,
      Directions.DirectionsText
FROM Readers, 
    AccessPoints, 
    Points_Readers,
    Transactions, 
    AccessNumbers,
    Users,
    Directions 
WHERE AccessPoints.PointID = Points_Readers.PointID
 AND Readers.ReaderID = Points_Readers.ReaderID
 AND AccessNumbers.AccessNumberID = Users.AccessNumberID
 AND Transactions.Number = AccessNumbers.Number
 AND Readers.ReaderID = Transactions.ReaderID
 AND Readers.Direction = Directions.Direction
 AND Transactions.Timestamp>#2018-08-15 06:50:16#
ORDER BY Transactions.Timestamp;

FROM 子句中的表或 WHERE 子句中的條件的順序在這些形式中無關緊要。

引用自:https://dba.stackexchange.com/questions/215095