Not sure if this will work but try recoding your WHERE clause
as an INNER JOIN and nest the LEFT JOIN inside it.
Sth like
SELECT C_ID, C_FirstName, C_LastName,
M_ID, ModelName, ModelYear
FROM Models
INNER JOIN (Customers LEFT JOIN Cust_Models ON
customers.C_ID=Cust_Models.Cust_ID)
ON Models.M_ID = Cust_models.CM_ID
WHERE C_ID=#form.C_ID#
Nick
-----Original Message-----
From: Cyrill Vatomsky [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 13, 2000 7:09 AM
To: CF-Talk
Subject: OT:MS Access and outer join - not supported?
Does MS Access support outer joins?
I am trying to run this query on a MS Access 2000 file via CFQUERY:
(Cust_Models has two columns: customer ID and Model ID. I want to retreive
customers even if they do not have any models associated with them in the
Cust_Models table.)
<cfquery>
SELECT C_ID, C_FirstName, C_LastName, M_ID, ModelName, ModelYear
FROM (Customers left join Cust_Models ON
customers.C_ID=Cust_Models.Cust_ID), Models
WHERE C_ID=#form.C_ID# and Cust_models.CM_ID = Models.M_ID
</cfquery>
I get this:
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Join expression not supported.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (8:1) to (8:58).
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
