Nested JOIN clauses - order of precedence (Access 2007)

  • Thread starter Thread starter Tokyo Alex
  • Start date Start date
T

Tokyo Alex

Dear all,

I'm currently working with a fairly complex query covering six tables. I've
basically got it sorted, but it's got me thinking about multiple, nested JOIN
clauses and their order of precedence.

It looks, from my experience, as though JOINs must be in brackets (if
nested) and are evaluated from the inside out. Access Help seems to indicate
this, also. I have read that this may not be the case, however.

Is my understanding correct?
If brackets do NOT indicate order of precedence, what is their purpose, and
why does Access require them?

To take a simple example:
A product has a category and a category has a subcategory.
So, tables:
Prod Cat SubCat
ID - PK ID - PK ID - PK
CatID - FK SubCatID - FK

In this case,
SELECT *
FROM Prod INNER JOIN Cat ON Prod.CatID = Cat.ID INNER JOIN SubCat ON
Cat.SubCatID = SubCat.ID;
fails with a “Syntax Error (Missing Operator)†error. This is expected, as
Access requires brackets.

SELECT *
FROM (Prod INNER JOIN Cat ON Prod.CatID = Cat.ID)
INNER JOIN SubCat ON Cat.SubCatID = SubCat.ID;
works, as does
SELECT *
FROM Prod INNER JOIN
(Cat INNER JOIN SubCat ON Cat.SubCatID = SubCat.ID) ON Prod.CatID = Cat.ID;

So it appears that, with all INNER JOINs at any rate, the actual order of
operations is not important.

But, maybe only some categories have subcategories. I still want to return
records for all products, so I need an OUTER JOIN on Cat and SubCat.

Access Help states, "A LEFT JOIN or a RIGHT JOIN may be nested inside an
INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT
JOIN.â€

So you’d think that
SELECT *
FROM Prod INNER JOIN
(Cat LEFT JOIN SubCat ON Cat.SubCatID = SubCat.ID) ON Prod.CatID = Cat.ID;
would work, but that
SELECT *
FROM (Prod INNER JOIN Cat ON Prod.CatID = Cat.ID)
LEFT JOIN SubCat ON Cat.SubCatID = SubCat.ID;
would fail because it has the INNER JOIN nested inside the LEFT JOIN.

In fact, they both work.

So, what’s going on? What, really, are the rules for nesting JOINS?

To extend the example, what if Prod also contained a SupID FK to the
supplier table? How should this be added to the query?

Very long post, I know; my thanks to anyone who made it this far <g>

Any ideas, advice, comments or pointers to good resources on this topic
would be very much appreciated.

Thanks in advance,
Alex.
 
Just realised that I've mistaken the logic for Prod-Cat-SubCat.

SubCat should, of course, have a CatID FK to establish the one-to-many in
the right direction.

So in the previous example, please substitute "Supercategory" for
"Subcategory", in a business sense possibly a "Line of Business" that
includes several categories.

Cheers,
Alex.
 
In fact, they both work

Interesting, did not work with Jet 3, which is where the help language would
have come from.

Error was "ambiguous outer join".

The query-by-example design view had no way of displaying the join priority,
so in the QBE design view the join was ambiguous, even though Access would
write SQL with brackets. Of course, if the query was ambiguous in the QBE
design view, there would have been no reason to make the Jet engine capable
of parsing a query like that.

(david)
 
Back
Top