Help with 'Join expression not supported'

[davegraham_1998]

Hi All-

I have a (simple) query trying to learn how to mix inner and outer
joins in access, I'm getting a 'Join expression not supported' error.

SELECT CUSTOMER.CUST_ID, CUSTOMER.CUST_NAME, TRANSACTION.PROD_ID,
PRODCUT.DESCR
FROM
(CUSTOMER LEFT JOIN TRANSACTION ON CUSTOMER.CUST_ID =
TRANSACTION.CUST_ID)
INNER JOIN PRODUCT ON TRANSACTION.CUST_ID = PRODUCT.PROD_ID

If I take the inner join out, then it works fine.

Any help will be greatly appreciated.

 

[Michel Walsh]

Hi,


If transaction.cust_id is present in customer.cust_id, the initial left join
can be replace by a simpler inner join.

If transaction.cust_id is NOT present in customer.cust_id, the value under
transaction.cust_id will be NULL, after the left join. ---BUT--- the final
inner join will REJECT that row, since NULL = something returns null.

In conclusion, your first left join is logically useless, in all cases, and
thus, you should use an inner join instead, if the logic of the problem is
reflected by the actually proposed statement.


Hoping it may help,
Vanderghast, Access MVP

 

[davegraham_1998]

Oops.....

I mistyped, it shoud read:

SELECT CUSTOMER.CUST_ID, CUSTOMER.CUST_NAME, TRANSACTION.PROD_ID,
PRODCUT.DESCR
FROM
(CUSTOMER LEFT JOIN TRANSACTION ON CUSTOMER.CUST_ID =
TRANSACTION.CUST_ID)
INNER JOIN PRODUCT ON TRANSACTION.PROD_ID = PRODUCT.PROD_ID

What I am trying to achieve from above is all the customers, even if
they have not placed an oreder yet, the product id and product descr in
the case where customers did place an orders.

1 JohnDoe 1999 product_1
1 JohnDoe 2000 product_2
2 JaneDoe 2000 product_2
3 DoeDoe null null

 

[david epsom dot com dot au]

Use the Access query designer to create your queries.

You have to get the brackets and the order of the
terms the right way around. The easiest way to do
this is to use the query designer.


The problem with your query as written is that
you can't do a full Join on a (Partial Join).

You can only do a Partial Join on a (full join).

This is a logical problem, which Vanderghast has
explained more correctly.

Try to do it in the Access query designer: seeing
the picture might help you understand the logic.

(david)

 

[Marshall Barton]

SELECT CUSTOMER.CUST_ID, CUSTOMER.CUST_NAME, TRANSACTION.PROD_ID,
PRODCUT.DESCR
FROM
(CUSTOMER LEFT JOIN TRANSACTION ON CUSTOMER.CUST_ID =
TRANSACTION.CUST_ID)
INNER JOIN PRODUCT ON TRANSACTION.PROD_ID = PRODUCT.PROD_ID

What I am trying to achieve from above is all the customers, even if
they have not placed an oreder yet, the product id and product descr in
the case where customers did place an orders.

1 JohnDoe 1999 product_1
1 JohnDoe 2000 product_2
2 JaneDoe 2000 product_2
3 DoeDoe null null

Another way of saying what Vanderghast and David are telling
you is that the parenthesis are in the wrong place:

FROM CUSTOMER LEFT JOIN
(TRANSACTION INNER JOIN PRODUCT
ON TRANSACTION.PROD_ID = PRODUCT.PROD_ID)
ON CUSTOMER.CUST_ID = TRANSACTION.CUST_ID

which is what you should get if you followed David's advice
and let the query designer create the SQL. Well, the query
designer might do it this way:

FROM (TRANSACTION INNER JOIN PRODUCT
ON TRANSACTION.PROD_ID = PRODUCT.PROD_ID)
RIGHT JOIN CUSTOMER
ON CUSTOMER.CUST_ID = TRANSACTION.CUST_ID

 

[Chris2]

Oops.....

I mistyped, it shoud read:

SELECT CUSTOMER.CUST_ID, CUSTOMER.CUST_NAME, TRANSACTION.PROD_ID,
PRODCUT.DESCR
FROM
(CUSTOMER LEFT JOIN TRANSACTION ON CUSTOMER.CUST_ID =
TRANSACTION.CUST_ID)
INNER JOIN PRODUCT ON TRANSACTION.PROD_ID = PRODUCT.PROD_ID

What I am trying to achieve from above is all the customers, even if
they have not placed an oreder yet, the product id and product descr in
the case where customers did place an orders.

1 JohnDoe 1999 product_1
1 JohnDoe 2000 product_2
2 JaneDoe 2000 product_2
3 DoeDoe null null

 

[davegraham_1998]

I should know this, but....

Partial Join: either a left or a right outer join?
Full Join: an inner join? or a left AND right outer join?

Am I right?

 

[John Spencer]

It sounds as if you also need to use a LEFT JOIN on the last join - the one
between Product and Transaction.

 

[Guest]

Yes.
http://www.devx.com/dbzone/Article/17403/0/page/4

I should know this, but....

Nope, I just made those terms up, because I was trying to think of different
ways to express what I was trying to say. I use this forum to clarify my
thinking and expose it to correction. (In moments of reflection I hope that
I am also sufficiently useful to make up for my sins).

The picture referenced above shows why the joins are called 'outer' and
'inner', but without the picture the terms are not self-explanatory. 'Left'
and 'Right' are not required for general discussion, and are unhelpful even
when you know what they mean.

I like Partial, because I think of a left inner join as being part of a full
inner join, an inner join with only part of the join critria, a partial
union of the join fields.

The inner join then becomes a full inner join because it is an inner join
with the full criteria. But although the derivation is sound, the result is
less happy: a full join could also be a full outer join, a full union of the
join fields. Tight? Complete? Bi-directional? Perfect?

regards
(david)