A resolution to this problem: Creating the view with the select IN & NOT IN then using the view in the GROUP BY & HAVING did the trick. Thanks Steve.
I still don't understand why the combined query didn't work and I tried everybody else's suggestions with no improvements I'm afraid. Regards, Alastair. ----- Original Message ----- From: "Steve Hartmann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, April 20, 2002 3:05 AM Subject: RE: Problem with select statement > 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/ ================================================ 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/
