> 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:238213 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
