If you take the GROUP BY off the SELECT, does it work correctly? If it does, try creating a view out of the SELECT without the GROUP BY and then use that view in your GROUP BY with the HAVING.
Stephen R. Hartmann SQL Resources Group [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Alastair Burr Sent: Friday, April 19, 2002 3:00 PM To: R:Base ListServer Subject: Problem with select statement Hi everyone, I'm trying to create a menu that displays names where the number of rows in the table is greater than 9 where certain other conditions also apply. I've tried what seems like a hundred variations on this select statement but I am not getting the results I want returned: SELECT FullName, DiscType_ID, ReleaseType_ID FROM Track_Zero_View + WHERE DiscType_ID IN (1,3,4,5,6,7,8,9,15,16,19,21,26,37,38,39,40,41,42,43,45) + AND ReleaseType_ID NOT IN (1,4,10,11) + GROUP BY FullName, DiscType_ID, ReleaseType_ID + HAVING COUNT(*) > 9 What I'm getting is rows where either DiscType_ID is NOT in the list and/or rows where ReleaseType_ID ARE in the list. The count > 9 appears to be working in that I am NOT getting names where the count is 9 or below. For instance, I get rows where DiscType_ID is 14 and/or Release_Type is 1. I only need the FullName returned (or its IdNum), the other two columns are there to try and see what is happening. If anybody has any ideas what I am doing wrong they would be greatly appreciated, Thanks in advance, Regards, Alastair. ---------------------------------- A D B Burr, St. Albans, UK. ---------------------------------- [EMAIL PROTECTED] ---------------------------------- ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
