Here's the error: -------------- Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Orders.Product_ID, Orders.Options, Order_No.DateOrdered ---------------
That SELECT comes from the subquery, but the subquery works fine by itself in 4 or 5...so it's something near that, not much help... -- Josh ----- Original Message ----- From: "Barney Boisvert" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Wednesday, April 19, 2006 3:29 PM Subject: Re: Query works MySQL 4 but not 5 >> Anything jump out? > > The lack of an error message from MySQL that indicates where the > syntax error occurred? > > cheers, > barneyb > > On 4/19/06, Josh Nathanson <[EMAIL PROTECTED]> wrote: >> All, >> >> Got a query that works fine in MySQL 4.1 but not MySQL 5, I get a SQL >> syntax >> error. It looks hairy but there are three tables involved: Orders, >> Order_No, and Products. The subquery joins Orders and Order_No, the main >> query joins the result of the subquery (alias ord) with Products (alias >> P). >> >> <cfquery name="qry_get_user_products" datasource="#Request.DS#" >> username="#Request.user#" >> password="#Request.pass#"> >> SELECT P.Product_ID, P.Name, P.SKU, ord.Options, ord.DateOrdered FROM >> (SELECT Orders.Product_ID, Orders.Options, Order_No.DateOrdered >> FROM >> Order_No LEFT JOIN Orders ON Order_No.Order_No = Orders.Order_No >> WHERE Order_No.User_ID = <cfqueryparam >> value="#attributes.user_id#" >> cfsqltype="CF_SQL_INTEGER">) >> ord >> LEFT JOIN Products P ON P.Product_ID = ord.Product_ID >> ORDER BY ord.DateOrdered DESC >> </cfquery> >> >> I tested the subquery by itself and it works fine in 4 and 5, produces >> the >> expected results, so it must be something about joining the result ord >> with >> Products P. Anything jump out? >> >> >> -- Josh > > -- > Barney Boisvert > [EMAIL PROTECTED] > 360.319.6145 > http://www.barneyb.com/ > > Got Gmail? I have 100 invites. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238215 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
