nesting of joins in the "from" clause

[joeblow2]

Can anyone please suggest something to read regarding the way Access nests joins in the "from" clause- all that amount of bracketing etc. Especially self joins.
Thanks.

 

[jdraw]

I did a search but could not find anything direct. However, I did find this in a response to a similar question in a different forum. I have found r937 very helpful and clear on many SQL related issues. Hope this helps.

access requires parentheses when there are more than two tables being joined
Code:

select ...
from (
personal_info
inner
join cpsPrograms
on personal_info.cpsp
= cpsPrograms.programID
)
inner
join pro_info
on personal_info.userID
= pro_info.pro_ID
where state = 'MD'
and allowSearch = 1


tip: please don't use the asterisk in the SELECT, list only the columns you really need

tip: qualify each column, because it is not immediately clear which table state and allowsearch belong to

tip: always use JOIN ... ON syntax, don't list the tables with the join conditions in the WHERE clause
__________________
r937.com | rudy.ca

Another responder went further with

I would like to add that if you have more INNER JOINs to do, always surround the whole with parentheses then add the extra INNER JOINs

original statement that works
SELECT
......
FROM (EDistrict AS ed
INNER JOIN tblVALD AS vd ON ed.ED_code = vd.VALD_int_ed)
INNER JOIN tblCOOR AS cr ON ed.ED_code = cr.COOR_int_ed

additional INNER JOINS

....

FROM ((EDistrict AS ed
INNER JOIN tblVALD AS vd ON ed.ED_code = vd.VALD_int_ed)
INNER JOIN tblCOOR AS cr ON ed.ED_code = cr.COOR_int_ed)
INNER JOIN tblABCD AS ab ON ed.ED_code = ab.ABCD_int_ed

....

FROM (((EDistrict AS ed
INNER JOIN tblVALD AS vd ON ed.ED_code = vd.VALD_int_ed)
INNER JOIN tblCOOR AS cr ON ed.ED_code = cr.COOR_int_ed)
INNER JOIN tblABCD AS ab ON ed.ED_code = ab.ABCD_int_ed)
INNER JOIN tblZZZZ AS zz ON ed.ED_code = zz.ZZZZ_int_ed

But the bracketing is an MS Access thing, and not necessarily applicable to other databases. Bracketing may cause syntax issues with other systems.

 

[joeblow2]

Thanks a lot jdraw!