My join isn't working - SQL help please!

[GravyMonster]

Hi guys,

This is a weird one. I've done hundreds of joins before but this one just will not work. Using MSSQL I've got two tables, one containing the names and details of mailing lists, the other contains its members. Here's a very simplified example...

MailingLists:
ID	Name
-------------------------------
1	Prospects
2	Customers
3	Suppliers

Members:
ID	Name	MailingListID
-------------------------------
1	Tom	1
2	Dick	1
3	Harry	1
4	Ed	3

What i want is a query to return each mailing list with how many members it contains - including empty mailing lists.

I've got this...

SELECT MailingLists.MailingListID, MailingLists.Name, COUNT(MailingLists.MailingListID) AS ActiveMembers
FROM MailingLists LEFT OUTER JOIN
                      MailingList_Members ON MailingLists.MailingListID = MailingList_Members.MailingListID
WHERE     (MailingLists.AccountID = XXX) AND (MailingLists.Removed = 0) AND (MailingList_Members.OptInConfirmed = 1)
GROUP BY MailingLists.MailingListID, MailingLists.Name
ORDER BY MailingLists.Name

but it's not returning the empty lists. Using the above example, that would return

ID	Name		ActiveMembers
-------------------------------
1	Prospects	3
3	Suppliers	1

Any help appreciated.

 

[GravyMonster]

Cheers Mickey, that worked perfectly!