Problems with More than 1 Left joins in MSAccess

Question

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

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".

The query goes like this:

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**

If I take out AND part, it works fine.

Any idea?

Answer 1

You are trying to match two fileds in the ON clause and any of the tables is MANAGERDETAILS.

I don't know what you are trying to get with this sql but you can either put the AND filter on the correct ON (after E.EID = D.EID) or you can change the tables used to filter in the current ON so one of the fields belongs to MANAGERDETAILS.

Answer 2

You need a second reference to the Employee table since you are trying to pull the manager's employee record.

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

We don't have enough information on the data you expect, but this query will execute.

I know in Access you'll need to handle the parentheses, but you could do this with the graphic query builder.

Answer 3

if you remove all parentheses from the statement, do you still get the error?

Answer 4

The way I would write this would be:

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.

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.