6 messages in com.mysql.lists.win32RE: Newbie Question - Query works in ...
FromSent OnAttachments
Ed Reed11 Nov 2002 15:26 
Ed Reed11 Nov 2002 23:54 
Alan McDonald12 Nov 2002 00:04 
Mark Armer12 Nov 2002 09:11 
Ed Reed12 Nov 2002 10:32 
jbon...@sola.com.au12 Nov 2002 14:04 
Subject:RE: Newbie Question - Query works in access but not MySQL
From:jbon...@sola.com.au (jbon@sola.com.au)
Date:11/12/2002 02:04:42 PM
List:com.mysql.lists.win32

Besides putting in the space before "Like" I think you should use "WHERE" instead of "HAVING". Access may well fix this up for you because although HAVING is almost the same as WHERE it is different. WHERE is nominally applied as records go into the query where HAVING is applied as they go out of the query. This means you can filter on the results of an aggregate etc. Since the conditions you have in the HAVING clause apply to fields straight from the tables they can just as well be done in a WHERE clause. Perhaps Access realises this and makes the adjustment whereas MySQL also realises that you should not be doing this in a HAVING clause and complains. The fact that the Obsolete field is not included in the output of the query is probably why the HAVING clause complains about that field. Shifting the conditions to a where clause means less records are processed too which should speed things although the Like expression you have probably means it has to scan the entire product table anyway .

John Bonnett

-----Original Message----- From: Ed Reed [mailto:ere@nearfield.com] Sent: Tuesday, 12 November 2002 6:24 PM To: mys@lists.mysql.com; win@lists.mysql.com Subject: Re: Newbie Question - Query works in access but not MySQL

UPDATE:

Still trying to solve this and I think I have something that's a little easier to understand. If I run the following query against MySQL it returns "Unknown column 'Obsolete' in 'having clause'". If I run the query using MSAccess as a frontend to MySQL the query runs correctly (it returns a recordset with 9 records). If I remove the Obsolete column from the Having clause the MySQL server appears to hang up while it processes the query but it never returns even if left for an hour.

Please, has anyone got any ideas?

SELECT Products.PartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID GROUP BY Products.PartNumber HAVING ((Products.PartNumberLike "%A-000%") AND (Products.Obsolete<>-1)) ORDER BY Products.PartNumber;