Returning a count of items including zero

[garydevstore]

Hi,
I have 2 tables, Mail_subject and Mail_Usage.

Mail_Subject contains the subject, body and some other bits of info.
CREATE TABLE [Waterford_MailSubject] (
[ID] [int] NOT NULL ,
[MailSubject] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[MailCategory] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[MailCreateDate] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Mail_Usage records the amount of times a certain mail was sent.
CREATE TABLE [Waterford_MailUsage] (
[ID] [int] NOT NULL ,
[RepScreenName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[MemberScreenName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[MailSubject] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[TimeDate] [smalldatetime] NULL ,

) ON [PRIMARY]
GO


They are joined by Subject (not my idea, its a DB ive inherited).

What i need is to get the Mail Subject and the number of times that
Mail was sent. Ive Joined them using an INNER JOIN which gave me a
count of the number of times each one occoured except for Mails that
have not been used. I need to get zero as the count of Mails not
sent. Ive tried a LEFT OUTER JOIN but it didnt work either.

Can someone point out what i need to do ?

[Hugo Kornelis]

On 24 May 2005 04:49:33 -0700, garydevstore wrote:

Hi,
I have 2 tables, Mail_subject and Mail_Usage. (snip)What i need is to get the Mail Subject and the number of times that
Mail was sent. Ive Joined them using an INNER JOIN which gave me a
count of the number of times each one occoured except for Mails that
have not been used. I need to get zero as the count of Mails not
sent. Ive tried a LEFT OUTER JOIN but it didnt work either.

Can someone point out what i need to do ?

Hi garydevstore,

Try if this works for you:

SELECT m.MailSubject,
(SELECT COUNT(*)
FROM MailUsage AS u
WHERE u.MailSubject = m.MailSubject)
FROM MailSubject AS m

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)