何时在 MS Access SQL 中打开和关闭连接周围的括号 [英] When to open and close brackets surrounding joins in MS Access SQL

问题描述

我想了解在 MS Access 查询中表示联接时何时打开和关闭括号，因为我正在使用 C++ 为 MS Access 查询开发查询构建器，以便我可以应用相同的代码来生成类似的查询.

I want to understand when to open and close brackets when representing joins in MS Access queries as I am developing a query builder using C++ for MS Access queries so that I can apply the same code to generate similar queries.

SELECT 
    MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID  
FROM 
    Tools AS MasterTool
    LEFT JOIN 
    (
        Toolsets LEFT JOIN Tools ON Toolsets.SlaveToolID = Tools.ID
    )
    ON MasterTool.ID = Toolsets.MasterToolID

@LeeMac 按照你的解释，当我修改了我之前提交给这个的查询

@LeeMac as per your explaination when i modified the query which i presented earlier to this

   SELECT Tools.Name, Toolsets.SlaveToolID, Tools.MachineID  FROM (Tools  
   LEFT JOIN  Toolsets ON Toolsets.SlaveToolID = Tools.ID )
   LEFT JOIN  Tools ON  Toolsets.MasterToolID = Tools.ID

我收到错误 Join Expression Not Supported 是否有任何简单的方法可以编写上述查询.

i am getting error Join Expression Not Supported is there is any simple way to write the above query.

推荐答案

本质上，当 MS Access 查询引用两个以上的表时，一对表之间的每个连续连接都应嵌套在括号内.

Essentially, when an MS Access query references more than two tables, every successive join between a pair of tables should be nested within parentheses.

例如，有两个表的查询不需要括号:

For example, a query with two tables requires no parentheses:

select *
from a inner join b on a.id = b.id

添加第三个连接表需要将原始连接周围的括号括起来，以便将其与附加连接区分开来:

The addition of a third joined table necessitates parentheses surrounding the original join in order to distinguish it from the additional join:

select *
from 
(
    a inner join b on a.id = b.id
) 
inner join c on a.id = c.id

表的每次连续添加都会导致现有连接嵌套在另一级括号中:

Every successive addition of a table will then cause the existing joins to be nested within another level of parentheses:

select *
from 
(
    (
        a inner join b on a.id = b.id
    ) 
    inner join c on a.id = c.id
)
inner join d on a.id = d.id

因此，一般来说:

select *
from 
(
    (
        (
            (
                table1 [inner/left/right] join table2 on [conditions]
            ) 
            [inner/left/right] join table3 on [conditions]
        )
        [inner/left/right] join table4 on [conditions]
    )
    ...
)
[inner/left/right] join tableN on [conditions]

LEFT/RIGHT 连接有一个微妙之处，因为嵌套的顺序必须保持连接的方向，例如:

There is a subtlety where LEFT/RIGHT joins are concerned, in that the order of nesting must maintain the direction of the join, for example:

select *
from 
(
    c left join b on c.id = b.id
) 
left join a on a.id = b.id

可以置换为:

select *
from 
c left join
(
    b left join a on b.id = a.id
)
on c.id = b.id

这篇关于何时在 MS Access SQL 中打开和关闭连接周围的括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！