Nested outer join as part of Cartesian product

  • Thread starter Thread starter Rhys N
  • Start date Start date
R

Rhys N

In Access 2000, is it not possible to return the Cartesian product of
two record sets where one of the record sets is the result of an outer
join?

Here is the query I want to run. The record set on the left-hand side
of the Cartesian product contains the outer (left) join. I get the
error message "Join expression not supported" when I try to run this.


SELECT * FROM
(
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])
), (
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])
)


If I change the LEFT JOIN to an INNER JOIN, everything works fine. I
can't imagine why the type of join should make any difference to
Access when it goes to calculate the Cartesian product.

Note I've checked that the following simple queries work okay:

1) SELECT * FROM
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])


2) SELECT * FROM
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])


3) SELECT * FROM
[Dealer Special Pricing], [Companies]


Any ideas?

Rhys
 
I think you have done fairly thorough testing so all I say
is JET SQL has a number of restrictions. One common
restriction that most people are not aware of is that you
can nest an Outer Join (Left / Right) inside an Inner Join
but you cannot nest an Inner Join inside an Outer Join.

I am sure there must be some technical reasons for JET to
have these restrictions.

In you case, you may like to try a set of 3 Queries.
Query1 and Query2 have the SQL Strings as point 1 & 3 in
your post and Query3 has the SQL String:

SELECT [Query1].*, [Query2].*
FROM [Query1], [Query2]

HTH
Van T. Dinh
MVP (Access)
 
I'm not sure what fields you are really interested in from each of
these subqueries, I would really recommend against using SELECT * if
you can help it, since you probably don't really need all the fields
from all of these tables, and the more fields you include in the
query, the longer it will take to return the information across a
network.

I think this should work.

Try it like:


SELECT D.*, C.*
FROM (SELECT DSP.*, CSP.*
FROM [Dealer Special Pricing] DSP
LEFT JOIN [Company Special Pricing] CSP
ON DSP.ProductID = CSP.ProductID
AND CSP.CustomerID = [Forms]![Customer
Pricing]![CustomerID]) AS D,
(SELECT Comp.*, ER.*
FROM Companies Comp
INNER JOIN [Exchange Rates] ER
ON Comp.QuotingExchangeRateId = ER.ExchangeRateZoneId
AND Comp.CompanyID = [Forms]![Customer
Pricing]![CustomerID]) AS C

--
HTH

Dale Fye


In Access 2000, is it not possible to return the Cartesian product of
two record sets where one of the record sets is the result of an outer
join?

Here is the query I want to run. The record set on the left-hand side
of the Cartesian product contains the outer (left) join. I get the
error message "Join expression not supported" when I try to run this.


SELECT * FROM
(
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])
), (
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])
)


If I change the LEFT JOIN to an INNER JOIN, everything works fine. I
can't imagine why the type of join should make any difference to
Access when it goes to calculate the Cartesian product.

Note I've checked that the following simple queries work okay:

1) SELECT * FROM
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])


2) SELECT * FROM
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])


3) SELECT * FROM
[Dealer Special Pricing], [Companies]


Any ideas?

Rhys
 
Van and Dale, thanks a lot for your suggestions. I'd thought of
splitting the original query into three queries but was wondering
whether what I was trying to achieve was possible using a single
query. Dale's SQL worked perfectly once I added brackets around the ON
clause of each join.

Rhys


Dale Fye said:
I'm not sure what fields you are really interested in from each of
these subqueries, I would really recommend against using SELECT * if
you can help it, since you probably don't really need all the fields
from all of these tables, and the more fields you include in the
query, the longer it will take to return the information across a
network.

I think this should work.

Try it like:


SELECT D.*, C.*
FROM (SELECT DSP.*, CSP.*
FROM [Dealer Special Pricing] DSP
LEFT JOIN [Company Special Pricing] CSP
ON DSP.ProductID = CSP.ProductID
AND CSP.CustomerID = [Forms]![Customer
Pricing]![CustomerID]) AS D,
(SELECT Comp.*, ER.*
FROM Companies Comp
INNER JOIN [Exchange Rates] ER
ON Comp.QuotingExchangeRateId = ER.ExchangeRateZoneId
AND Comp.CompanyID = [Forms]![Customer
Pricing]![CustomerID]) AS C

--
HTH

Dale Fye


In Access 2000, is it not possible to return the Cartesian product of
two record sets where one of the record sets is the result of an outer
join?

Here is the query I want to run. The record set on the left-hand side
of the Cartesian product contains the outer (left) join. I get the
error message "Join expression not supported" when I try to run this.


SELECT * FROM
(
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])
), (
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])
)


If I change the LEFT JOIN to an INNER JOIN, everything works fine. I
can't imagine why the type of join should make any difference to
Access when it goes to calculate the Cartesian product.

Note I've checked that the following simple queries work okay:

1) SELECT * FROM
[Dealer Special Pricing] LEFT JOIN [Company Special Pricing] ON
([Dealer Special Pricing].ProductID = [Company Special
Pricing].ProductID AND [Company Special Pricing].CustomerID =
[Forms]![Customer Pricing]![CustomerID])


2) SELECT * FROM
Companies INNER JOIN [Exchange Rates] ON
(Companies.QuotingExchangeRateId = [Exchange Rates].ExchangeRateZoneId
AND Companies.CompanyID = [Forms]![Customer Pricing]![CustomerID])


3) SELECT * FROM
[Dealer Special Pricing], [Companies]


Any ideas?

Rhys
Click to expand...
 
Back
Top