How about if you rewrite the statement to: > SELECT Products.PartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty > FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = > tblInvTransaction.ProductID > Where ((Products.PartNumberLike "%A-000%") AND > (Products.Obsolete<>-1)) > GROUP BY Products.PartNumber;
Mark -----Original Message----- From: Alan McDonald [mailto:alan@;meta.com.au] Sent: Tuesday, November 12, 2002 02:05 To: Ed Reed; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Newbie Question - Query works in access but not MySQL Do you have a space between PartNumber and Like? Also there's not need to ORDER BY - the GROUP BY does that anyway (I know that's the case elsewhere) and finally, I have to guess that it's objecting to Obsolete not being in the main select.. have you tried including it and grouping by it? you can ignore the obsolete in the main select when it's returned since it will all be the same value... put it first. Alan > -----Original Message----- > From: Ed Reed [mailto:ereed@;nearfield.com] > Sent: Tuesday, 12 November 2002 18:54 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > 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; > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
