Join expression not supported with outer joins

[corybradshaw]

I have a query that works in SQL Server, but when I attempt to run it
in Access 2003 it tells me that "Join expression not supported". I can
build the query with the Access query builder, and it will give me a
functioning query, but when I copy it and try to run it in SQL View it
will give me the error again. Here is the query:

SELECT * FROM ((DigActivity LEFT JOIN Nominal_Diameter ON
DigActivity.ODNominal = Nominal_Diameter.Diameterid) INNER JOIN Tasks
ON DigActivity.TaskID = Tasks.Taskid) LEFT JOIN GPS_Data ON
DigActivity.GPSID = GPS_Data.GPSID, Units INNER JOIN Unit_Assign ON
Units.UnitID = Unit_Assign.UnitID WHERE (((Tasks.Taskid)=246) AND
((Unit_Assign.UnitTypeID)=5) AND ((Unit_Assign.UnitSetID)=1));

I have read that in Access there is an issue with regards to what order
you put your joins in. However, how do you handle ordering the joins
with the second part of the query (Units INNER JOIN Unit_Assign)? The
query will run if I remove the Units portion, but regardless of where I
put it in the query I receive the "Join expression not supported"
error.

Is there something I'm missing?

 

[Brendan Reynolds]

Perhaps I'm missing something, but isn't there a JOIN missing there? I don't
see a JOIN clause that specifies how the result of Units INNER JOIN
Unit_Assign ON Units.UnitID = Unit_Assign.UnitID is joined to the rest of
the query?

 

[John Spencer]

Access requires parentheses to determine the order of the joins (even when
they shouldn't be required). You might try the following UNTESTED
modification

SELECT *
FROM (((DigActivity LEFT JOIN Nominal_Diameter
ON DigActivity.ODNominal = Nominal_Diameter.Diameterid)
INNER JOIN Tasks
ON DigActivity.TaskID = Tasks.Taskid)
LEFT JOIN GPS_Data ON
DigActivity.GPSID = GPS_Data.GPSID)
,
(Units INNER JOIN Unit_Assign
ON Units.UnitID = Unit_Assign.UnitID )
WHERE (((Tasks.Taskid)=246) AND
((Unit_Assign.UnitTypeID)=5)
AND ((Unit_Assign.UnitSetID)=1));

If that doesn't work you may have to use an embedded subquery.

 

[Cory Bradshaw]

There is no missing join. The result of (Units INNER JOIN Unit_Assign)
is a single row that is not dependant on the rest of the query (which
also returns a single row). The purpose of the connection is to create
a single query that can be handed off to Crystal Reports.

My concern is that when the comma is used to select several tables,
Access treats this the same way as it handles joins, and it is subject
to the fickle joining order of Access. Is there any other way to write
the functionality of the comma when there aren't any fields that you
are joining on?

 

[Brendan Reynolds]

OK, it's a Cartesian join, but apparently a deliberate one. So I ran some
tests, and I find I'm able to duplicate your problem using outer joins and a
Cartesian join. The problem goes away if I change the outer joins to inner
joins. So I'm wondering if this is a limitation of the database engine.
Maybe you just can't combine outer joins and a Cartesian join like that?
Let's try it with a simple example. First, we'll use an inner join and a
Cartesian join ...

SELECT Products.*, Suppliers.*, Employees.*
FROM Employees, Products INNER JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID;

OK, this works. Now let's change the inner join to an outer join ...

SELECT Products.*, Suppliers.*, Employees.*
FROM Employees, Products LEFT JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID;

And, sure enough, this causes the error, "join expression not supported".
Just out of curiosity, I tried it in SQL Server. It works in SQL Server, so
it seems this may be a limitation of the JET database engine.

Do you need to return more than one column from the result of (Units INNER
JOIN Unit_Assign)? If not, you could probably do it with a sub-query. If you
do need to return more than one column, then I'm afraid I don't know what
the solution is.

 

[Cory Bradshaw]

I was actually using the result of the (Units INNER JOIN Unit_assign)
to format the data in the query into the correct units. However, I was
able to replace each instance of the unit conversion with a subquery.
It is ugly, but it works. Thanks for the help!