Implicit JOIN to Explicit with 3 non-nested tables

[Guest]

This query with implicit JOIN works fine.
select * FROM A, B, C
WHERE C.ID = A.CID
AND B.ID = A.BID

table A
ID
BID
CID

table B
ID

table C
ID

To change the SELECT to use the EXPLICIT JOIN syntax, one example says this
should work

select * FROM A
INNER JOIN B ON B.ID = A.BID
INNER JOIN C ON C.ID = A.CID

However, Access doesn't accept this syntax. (Syntax error, missing
operator....)
The individual explicit JOINs work fine separately, like these two:
select * FROM A
INNER JOIN B ON B.ID = A.BID

select * FROM A
INNER JOIN C ON C.ID = A.CID


Note that I do NOT want nested JOINs here.

Almost all the examples I've seen for Access and SQL-Server are limited to 2
tables unless nesting is used (hundreds of useless duplicate examples
available on-line). In this case, table A is really an intersection table
that just links B and C. I know how to get the results I want using this
syntax:

SELECT * FROM B
INNER JOIN (A
INNER JOIN (C)
ON C.ID = A.CID)
ON B.ID = A.BID

Note that A is the middle table in this JOIN, so this doesn't seem to be the
direct equivalent of the first query, yet they return the same results.
(Only the fields are in different order, but all the same fields appear.)

Changing the table order to this doesn't work:
SELECT * FROM A
INNER JOIN (B
INNER JOIN (C)
ON C.ID = A.CID)
ON B.ID = B.BID

Even Joe Celko's books seem to omit this situation.

Please tell me where I can find the non-nested syntax that is the EXPLICIT
JOIN equivalent of the implicit JOIN in the first query. (Or does Access
re-format the original implicit JOIN query such that A is the middle table,
so my solution is in fact the equivalent of the original, and thus the only
solution? If that's the case, I wonder how to solve this if the intersection
table brings together more then 2 other tables.)

 

[Guest]

the last code example is not right. Should have been:

SELECT * FROM A
INNER JOIN (B
INNER JOIN (C)
ON C.ID = A.CID)
ON B.ID = A.BID

But I think I know why this doesn't work anyway.... which may prove why the
intersection table must be the middle table unless each ON clause is allowed
to follow its own JOIN.

 

[Gary Walter]

I would have thought

SELECT * FROM
(A INNER JOIN B
ON A.BID = B.ID)
INNER JOIN C

 

[Guest]

That works. Thanks a bunch. I guess the problem was just the location of
the parentheses. I don't know how the other working examples of paren usage
wold have lead me to this solution.

 

[Michel Walsh]

The problem was like a problem of variable scope. You have



(B
INNER JOIN (C)
ON C.ID = A.CID)


ie, trying to define the join between tables B and C, you mentioned an
unknown table (at that scope level) in the ON clause.




Vanderghast, Access MVP