主页 / dba / 问题 / 215095
Accepted
Agi Hammerthief
Asked: 2018-08-17 06:39:21 +0800 CST

扩展查询以包含附加列

  • 772

我有以下查询，它只给我（除了其他列）方向 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通过加入ONReaders.Direction来扩展查询以包括在内。我在确定连接的确切位置时遇到了麻烦，这样我就不会一直在子句中出错。（我仍然没有弄清楚如何让子句与 Access 一起工作需要使用括号进行连接。）DirectionsDirections.DirectionFROMINNER JOIN

中的列Directions是Direction（数字）和DirectionText（字符串）

join ms-access

1 个回答

  1. Best Answer

    我在确切放置连接的位置时遇到了问题

    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 子句中出错

    使用查询生成器构建查询文本 - 如果存在任何逻辑问题，它总是会创建正确的代码或产生错误消息。

    或者（如果你想在不使用 master 的情况下“手动”编写查询文本）将查询重写为笛卡尔形式（使用逗号分隔的表列表和 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 子句中的条件在这些形式中无关紧要。

    • 0

相关问题