Join
擴展查詢以包含附加列
我有以下查詢,它只給我(除了其他列)方向 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
來擴展查詢以包含Directions
ONDirections.Direction
。我在準確放置連接的位置時遇到了麻煩,這樣我就不會在FROM
子句中不斷出現錯誤。(我還沒有弄清楚如何讓INNER JOIN
使用 Access 的子句需要使用括號進行連接。)中的列
Directions
是Direction
(數字)和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 子句中的條件的順序在這些形式中無關緊要。