sorry...here is the error I get: Error Diagnostic Information
ODBC Error Code = 42000 (Syntax error or access violation) [MySQL][ODBC 3.51 Driver][mysqld-3.23.53-max]You have an error in your SQL syntax near 'LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.Aut' at line 5 SQL = "SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHERE t.transcriptstatus = 20" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:1) to (18:57) in the template file C:\Inetpub\wwwroot\temp\mytemplate.cfm. Anyhow, if anybody has a clue about this, it would be GREATLY appreciated. Thanks! Trace ----- Original Message ----- From: "Trace" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, December 04, 2002 3:09 PM Subject: OT:SQL syntax error in mySQL > Hi everybody, > > I have just switched a cf site from using a MSAccess database to mySQL . The > following query produces and error now (did work in MSAccess): > > SELECT t.transID, a.authorID, g.groupID, ts.transName > FROM (tbl_authors a INNER JOIN tbl_groups g > ON a.GroupID = g.groupID) > INNER JOIN > (tbl_transcripts t LEFT JOIN tbl_transcriptionists ts > ON t.TransID=ts.TransID) > ON a.AuthorID = t.AuthorID > WHERE t.status = 20 > > > If I take out the LEFT JOIN statement and use this query instead, it works > just fine: > > SELECT t.transID, a.authorID, g.groupID > FROM (tbl_authors a INNER JOIN tbl_groups g > ON a.GroupID = g.groupID) > INNER JOIN > tbl_transcripts t > ON a.AuthorID = t.AuthorID > WHERE t.status = 20 > > I am new to mySQL. Is there a limit to the number of nested joins in a > query? The error I get is: > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm