Outer Join In Access SQL

[bholm]

I am using the following query in a Cold Fusion page. The server says this is not a supported statement:

"Error Diagnostic Information
ODBC Error Code = S1000 (General error)

[Microsoft][ODBC Microsoft Access Driver] Join expression not supported."

Here is the query:

SELECT eventLocID, stateprov, city, contact, phone1, eventName, eventDate, eventDetails
FROM Locations
LEFT JOIN Events ON ID = eventLocID
WHERE StateProv = 'Oregon'
GROUP BY eventLocID, stateProv, city, contact, phone1, eventName, eventDate, eventDetails
ORDER BY StateProv, City, contact

Is this a syntax error or is it really not supported in Access SQL?

 

[Quehay]

You've got a GROUP BY clause without any (evident) SQL aggregate functions--try removing that line.

 

[bholm]

I removed the GROUP BY but this did not change the results.
I've had this type of syntax difficulty before working with date syntax, I'm actually not sure what flavor of SQL to use. I try Oracle SQL and it almost seems to work but the error clearly says it's ODBC Microsoft Access Driver. But it clearly states in the error that the join I specified is not supported.

If I only knew where a reference book is, that accurately reflects the type of SQL my host uses.

Any ideas?

 

[Quehay]

What about creating the query in the Access container and treating it as a stored procedure using ADO? Now you won't be asking your client to perform the SQL (maybe it doesn't support the ANSI 92 syntax).

 

[nabiS2K]

How about an Access Union Query? Depending on what you are trying to accomplish, it could work. Let me know if you need explanation on how to use a Union Query (a union query is basically how access does an outer join). God Bless
Mike ;-)