Your parenthesis look unbalanced. Check them out. On 12/4/02 3:19 PM, "Trace" <[EMAIL PROTECTED]> wrote:
> 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
