Hi Alastair, This seems a little weird. I would try putting quotes around each item in the IN and NOT IN strings and see if that makes a difference. Does it act like the where clause is not even there? If so the quotes could make a difference. I am assuming the columns are probably integers. If they are text then you really need the quotes.
Best regards, Mike Young On Fri, 19 Apr 2002 20:59:46 +0100, Alastair Burr wrote: >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/
