Left Join problem

[Gary]

I have three tables A, B, C with no relationships defined between them. The
first two queries below work, the third gives an error "Join expression not
supported".

SELECT * FROM A INNER JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;
SELECT * FROM A RIGHT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;
SELECT * FROM A LEFT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;

What's wrong?
Thanks,
Gary

 

[Billy Yao [MSFT]]

Hi Gary,

Thank you for using MSDN Newsgroup! My name is Billy and I will be assisting you on this
issue.

From your description, I understand that you are using nested joins to get the recordset from
three underlying tables. However, you receive the error message "Join expression not
supported" in the following query (another two work fine):

SELECT * FROM A LEFT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =B.keyB;

Have I fully understood you? If there is anything I misunderstood, please feel free to let me
know.

=================
Based on my research, it is a known issue and also a by-design one. I can reproduce your
issue with the following steps:

1. Create three table as below:

Table: A
Two Columns: KeyA, Col2
Three records: 1,1 2,2 3,3

Table: B
Two Columns: KeyB, Col2
Two records: 1,1 2,2

Table: C
Two Columns: KeyC, Col2
Three records: 1,1

2. Create a query with the following statement:

----------------------
SELECT * FROM A INNER JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;
Works fine. The result is: 1,1,1,1,1,1

----------------------
SELECT * FROM A RIGHT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA
=B.keyB;
Works fine. The result is: 1,1,1,1,1,1

----------------------
SELECT * FROM A LEFT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =B.keyB;
Fails and get the error message "Join expression not supported"


=================
With the help of senior engineer's debugging, we can see that the "To" side of the outer join
cannot be a "inner join", which is a by design issue.

The problem is that it violates the following rule:

/*** Scan through the list of Inner joins and make sure that ***/
/*** the TO side of the Outer Join does not participate in any ***/
/*** Inner Joins i.e. A -> B - C is order-dependent ***/
PLNPL plnplIJ = (PLNPL) &pstate->lnplIJ;
PLN plnIJ = (PLN) plnplIJ->rgln;
PLN plnIJMac = plnIJ + plnplIJ->ilnMac;
for (;plnIJ < plnIJMac; plnIJ++)
{
if (pln->ptblTo == plnIJ->ptblFrom || pln->ptblTo == plnIJ->ptblTo)
/* Ambigous. Can not present in V1 */
AbortSQLParseErrInfo
(pstate,NULL,JET_errV11NotSupported,JET_errV11Ambiguous);
}

LEFT/RIGHT join has order, but inner join does not. The "To" (->) side of the outer join cannot
be a "inner join". This causes the ambiguous error ('A -> B - C' or 'A -> C - B').

Gary, does this answer your question? If you have any questions or concerns, please feel free
to post it in the group. I am standing by to be of assistance. Thanks again for using MSDN
Newsgroup!

Best regards,

Billy Yao
Microsoft Online Support

 

[Simson]

Hi,

In your Third SQL statement (Left Join) you're asking for
ALL the records in Table A and only related records in
Table B, and you're asking for those records in Table B to
be related to a record in Table C. This will result in an
error, because the order of your Joins is not correct. By
using the Left Join, the SQL is going to return ALL the
records in Table A, regardless of whether there is a
related record in Table B or not. And because the Left
Join is the only filter on Table A, the SQL will show ALL
the records in Table A because that is what Left Joins do.
But now you are trying to filter the records in Table B
with an Inner Join with Table C, but this has nothing to
do with your Left Join, because the Left Join is going to
show all the records in Table A, whether there is al
related record in Table B (filtered with Table C or not)
or not. You're basically asking for ALL the records in
Table A, but not all the records in Table A. It is not
making logically sense, and that is why you get the error.
You can use 2 queries to overcome the Join expression
error, for example:

Query 1:
SELECT B.*
FROM B INNER JOIN C ON B.keyB = C.keyC;

Query 2:
SELECT A.*
FROM A LEFT JOIN Query1 ON A.keyA = Query3.keyB;

But this will give you the exact same results as just Left
Joining Table A with Table B:

Query 3:
SELECT A.*
FROM A LEFT JOIN B ON A.keyA = B.keyB;

Hope this will answer your question.

Simson

 

[Gary]

If I understand your logic correctly, then a LEFT/LEFT combination should
fail for the same reason, but in fact it works, putting in nulls for the
missing fields on tables B and C. See my example with data, later in this
thread.

Gary

 

[Gary]

Sorry, I'm still not understanding the logic of the design. Let me approach
slightly differently using example data similar to Billy Yao's:

A
1, DataA1
2, DataA2
3, DataA3

B
1, DataB1
2, DataB2

C
1, DataC1

Focusing on tables B and C for the moment, consider these two recordsets:


SELECT * FROM B LEFT JOIN C ON B.keyB = C.keyC;
This produces resulting recordset using null's for unmatched fields in C:
B.keyB, B.dataB, C.keyC, C.dataC
======, =======, ======, =======
1, DataB1, 1, DataC1
2, DataB2, null, null


SELECT * FROM B INNER JOIN C ON B.keyB = C.keyC;
This produces resulting recordset:
B.keyB, B.dataB, C.keyC, C.dataC
======, =======, ======, =======
1, DataB1, 1, DataC1


So far, so good. To me, they are both perfectly valid recordsets. However,
if we now embed either of these into a left join, the first one passes while
the second fails.


SELECT * FROM A LEFT JOIN (B LEFT JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;
This produces resulting recordset:
A.keyA, A.dataA, B.keyB, B.dataB, C.keyC, C.dataC
======, =======, ======, =======, ======, =======
1, DataA1, 1, DataB1, 1, DataC1
2, DataA2, 2, DataB2, null, null
3, DataA3, null, null, null, null


SELECT * FROM A LEFT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;
This produces "Join operation not supported", but why couldn't it just fill
in nulls
for the missing records from the sub-recordset like the LEFT/LEFT join
above?

Thanks for your patience,
Gary

 

[Billy Yao [MSFT]]

Hi Gary,

Thank you for your update and asking for further assistance.

I understand that in theory the nested join (A -> B - C) is logical and it should return the
expected recordset by fill in nulls for the missing records from the sub-recordset.

# Note # I use '->' for LEFT JOIN, '<-' for RIGHT JOIN and '-' for INNER JOIN

However, as we known, LEFT/RIGHT join has order, but inner join does not. That means B - C
equals to C - B, and the statement "A LEFT JOIN (B LEFT JOIN C ON B.keyB = C.keyC) ON
A.keyA =B.keyB;" can be parsed to A -> B - C or A -> C - B. This will cause the ambiguous
error so that Jet engine doesn't support such an order-dependent nested join operation. To
avoid the ambiguous statement, the "To" (->) side of the outer join cannot be an inner join such
as A -> (B - C). This is a by-design rule as I mentioned in the previous message.

For detailed information, see the debugging result when performing the statement:

SELECT * FROM A LEFT JOIN (B INNER JOIN C ON B.keyB = C.keyC) ON A.keyA =
B.keyB;

=========================================
The problem is that it violates the following rule:

/*** Scan through the list of Inner joins and make sure that ***/
/*** the TO side of the Outer Join does not participate in any ***/
/*** Inner Joins i.e. A -> B - C is order-dependent ***/
PLNPL plnplIJ = (PLNPL) &pstate->lnplIJ;
PLN plnIJ = (PLN) plnplIJ->rgln;
PLN plnIJMac = plnIJ + plnplIJ->ilnMac;
for (;plnIJ < plnIJMac; plnIJ++)
{
if (pln->ptblTo == plnIJ->ptblFrom || pln->ptblTo == plnIJ->ptblTo)
/* Ambigous. Can not present in V1 */
AbortSQLParseErrInfo
(pstate,NULL,JET_errV11NotSupported,JET_errV11Ambiguous);
}
=========================================


Thank you for using MSDN Newsgroup!

Best regards,

Billy Yao
Microsoft Online Support

 

[Simson]

Hi,

This is really difficult to explain and I'll try my best.
The Left/Left join will work fine, but not the Left/Inner
join. The reason for this is, by LEFT joining Table B and
Table C, you are not blocking any records of Table B to be
shown, so you are not blocking any records of Table A to
be show in the result. And if you Left Join Table A with
Table B, it's going to attempt to show ALL the records in
Table A, and since you're not blocking any records in
Table B to be shown (because of the Left join with Table
C), the SQL compiler will have no problem. But if you
Inner Join Table B and Table C, you are blocking records
of Table B not to be shown, (you're only showing records
in Table B that have related records in Table C). Now, the
compiler is going to attempt to show ALL the records in
Table A, but you're blocking some records in Table B not
to be shown, so you are blocking it from showing some
records in Table A, but the Left Join is for showing All
the records. So in the first place you're asking for All
records (because of the Left Join between Table A and
Table B), but in the second place you're blocking some
records (because of the Inner Join between Table B and
Table C), and this is conflicting because you can not ask
for ALL records in Table A on the one hand and blocking it
from showing ALL records in Table A on the other hand, if
you understand what I mean. The best way to solve this is
by using 2 queries, with one a subquery within the other,
like in my previous example. It should give you the
desired results.

Simson

 

[Simson]

See my reply below your're example with data later in this
thread.

Simson

 

[Gary]

Thanks for both your replies. My conclusion is that this nesting constraint
stems from implementation. Theoretically I can still envision a scheme
whereby the nested recordset, once created, exists on its own and is
independent from the underlying tables and as such is fully able to
participate in a left join. I understand that an implementation may not
support this functionality due to "blocking" (as Simson says) or ambiguity
(as Billy says).

I think we've run this into the ground, thanks again.
Gary