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.

 

[Mickey]

Well I can see two obvious problems from your query.

1. You're counting MailingLists.MailingListID, it needs to be a column from your users table or it'll show a 1, even if there are no users
2. And "MailingList_Members.OptInConfirmed = 1", that value will be NULL if there are no users. "ISNULL(MailingList_Members.OptInConfirmed,1) = 1" should sort it

Mick

 

[GravyMonster]

Cheers Mickey, that worked perfectly!