G
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.)
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.)