MSAccess中超过1个左联接的问题 [英] Problems with More than 1 Left joins in MSAccess

问题描述

这与我以前的问题有关 MSAccess中有1个以上的左联接

This is related to my previous question More than 1 Left joins in MSAccess

问题是我有3个左联接，后跟一个AND运算符来检查1个条件.

The problem is that I have 3 left joins followed by an AND operator to check 1 condition.

如果我跑步，则会收到错误消息不支持联接表达式" .

If I run, then I am getting an error "Join Expression not supported".

查询如下:

SELECT * FROM(( EMPLOYEE AS E  LEFT JOIN DEPARTMENT AS D ON E.EID=D.EID)
                 LEFT JOIN MANAGERS M ON D.DID=M.DID)
             LEFT JOIN MANAGERDETAILS MD  ON M.MDID=MD.MDID
 **AND E.ENO=MD.ENO**

如果我拿出AND部分，它就可以正常工作.

If I take out AND part, it works fine.

有什么主意吗?

推荐答案

我的写法是:

SELECT EDM.*, MANAGERDETAILS.*
FROM (
    SELECT ED.*, MANAGERS.*
    FROM (
        SELECT EMPLOYEE.*, DEPARTMENT.*
        FROM EMPLOYEE
        LEFT JOIN DEPARTMENT
        ON EMPLOYEE.EID = DEPARTMENT.EID
    ) AS ED
    LEFT JOIN MANAGERS
    ON ED.DID = MANAGERS.DID
) AS EDM
LEFT JOIN MANAGERDETAILS
ON EDM.MDID = MANAGERDETAILS.MDID
    AND EDM.ENO = MANAGERDETAILS.ENO

基本上，您一次连接一个表，并对结果进行别名，然后将其用于下一次连接.

Basically, you join tables one at a time and alias the result that you can then use for the next join.

仅通过别名较小的子集，就可以在Access中实现任意复杂的Left联接. 您的double join子句可能不起作用，因为它的一个成员所引用的结果集比在该查询级别上可见的结果集要深.

You can achieve arbitrarily complex Left joins in Access only by aliasing smaller subsets. Your double join clause probably doesn't work because one of its members refers to a deeper resultset than the one that's visible at that level of the query.

这篇关于MSAccess中超过1个左联接的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！