Prev: how do i fix and type mismatch in expression
Next: Prevent the table modification message in action query
From: Tokyo Alex on 6 Apr 2010 05:11
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.

From: Tokyo Alex on 6 Apr 2010 05:21
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.


"Tokyo Alex" wrote:

> 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.
>
From: david on 7 Apr 2010 07:54
>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)


"Tokyo Alex" <TokyoAlex(a)discussions.microsoft.com> wrote in message
news:795436DC-9F01-416E-B574-A8A5B770C829(a)microsoft.com...
> 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.
>


 | 
Pages: 1
Prev: how do i fix and type mismatch in expression
Next: Prevent the table modification message in action query