That just gives me the ones that are missing, I wanted a set that shows
both.
I got the results I wanted using an OUTER JOIN
SELECT series#,COUNT(*) +
FROM records +
LEFT OUTER JOIN remarks ON records.series# = remarks.series# +
GROUP BY records.series#
Thanks Buddy
Walker, Buddy wrote:
James
I hit the send button before I wanted to.
I would flip-flop the tables and then use the NOT IN from remarks
SELECT series#,count(*) +
FROM records WHERE series# NOT IN (SELECT series# FROM remarks) +
GROUP BY series#
Buddy
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of James
Hageman
Sent: Wednesday, December 22, 2004 9:54 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - trying to find where count(*) = 0
In the following command;
SELECT series#,count(*) +
FROM remarks WHERE series# IN (SELECT series# FROM records ) +
GROUP BY series#
I get results as;
series# count (*)
---------- ----------
6315 1
6492 2
6493 1
6558 2
6567 2
6568 1
6569 2
6575 4
6579 1
6756 1
But I would also like to know when count is 0,zero in remarks
ie when the is a row in RECORDS but nothing in REMARKS
Do I UNION a NOT IN somewhere?
James
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [EMAIL PROTECTED]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
