Alistair, try this and see what happens. This method will use indexes. If you have a
quicker way to load the temp table, so much the better.
CREATE TEMP TABLE TmpReleaseIDs (ReleaseType_ID INT)
LOAD TmpReleaseIDs
1
3
4
5
6
7
8
9
15
16
19
21
26
37
38
39
40
41
42
43
45
END
CREATE INDEX TmpRelID on TmpReleaseIDs (ReleaseType_ID)
SELECT FullName, DiscType_ID, ReleaseType_ID +
FROM Track_Zero_View t1, TmpReleaseIDs t2 +
WHERE t1.ReleaseType_ID = t2.ReleaseType_ID +
AND ReleaseType_ID <> 1 +
AND ReleaseType_ID <> 4 +
AND ReleaseType_ID <> 10 +
AND ReleaseType_ID <> 11 +
GROUP BY FullName, DiscType_ID, ReleaseType_ID +
HAVING COUNT(*) > 9
"Alastair Burr" <[EMAIL PROTECTED]> 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/
>
__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas. Experience the
convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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/
