What's the deal with ( tblEmployee.EmployeeID IS NOT NULL ) ? Shouldn't your ON clause be relating one row in one table to a row in another table?
----- Original Message ----- From: "Paul Ihrig" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, June 30, 2002 9:57 AM Subject: INNER JOIN > can you see whats wrong with this query? > error below > thanks > -paul > > <cfquery name="rsServices" datasource="ISresource"> > SELECT tblRelations.Relation_ID, > tblService.Service_ID, tblService.Service, > tblEmployee.EmployeeID, > LEFT(LTRIM(tblEmployee.LastName),1) + '.' + > LEFT(LTRIM(tblEmployee.FirstName),1) + '.' AS > employeeinitial > > FROM tblService > INNER JOIN tblEmployee ON ( tblEmployee.EmployeeID IS NOT NULL ) > LEFT JOIN tblRelations ON ( > tblRelations.EmployeeID = tblEmployee.EmployeeID AND > tblRelations.Service_ID = tblService.ServiceID ) > WHERE EXISTS (SELECT EmployeeID FROM tblRelations > WHERE tblRelations.EmployeeID = tblEmployee.EmployeeID) > ORDER BY tblService.Service, tblEmployee.LastName, > tblEmployee.Firstname > > </cfquery> > > [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression > '( tblEmployee.EmployeeID NOT NULL ) LEFT JOIN tblRelations ON ( > tblRelations.EmployeeID = tblEmployee.EmployeeID AND tblRelations.Service_ID > = tblService.ServiceID )'. > > > > The error occurred while processing an element with a general identifier of > (CFQUERY), occupying document position (2:1) to (2:51). > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
