My host is actually using MySQL 3, not 5 as I thought, and subqueries are not allowed on MySQL 3. Crap.
-- Josh ----- Original Message ----- From: "Josh Nathanson" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Wednesday, April 19, 2006 3:40 PM Subject: Re: Query works MySQL 4 but not 5 > 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" <[email protected]> > 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:238219 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
