Have a view as follows:
SELECT dbo.ContactList.ContactListID, dbo.ContactList.ContactList, ISNULL(dbo.ContactList.Description, '-') AS Description, COUNT(dbo.ContactListContact.ContactID) AS ContactCount, dbo.ContactList.IsDeleted,
FROM dbo.ContactList LEFT OUTER JOIN
dbo.ContactListContact ON dbo.ContactListContact.ContactListID = dbo.ContactList.ContactListID LEFT OUTER JOIN
dbo.Contact ON dbo.ContactListContact.ContactID = dbo.Contact.ContactID
WHERE (dbo.ContactList.IsDeleted = 0) AND (dbo.ContactListContact.IsDeleted = 0) AND (dbo.Contact.IsDeleted = 0)
GROUP BY dbo.ContactList.ContactListID, dbo.ContactList.ContactList, dbo.ContactList.Description, dbo.ContactList.IsDeleted, dbo.ContactList.MailChimpListID
The WHERE clause is to ensure that I only get valid records into my list of contactlists. THis view is essentially returning a list of contactlists with the totals of members in each. The only issue with it is that if a contact list exists that doesn't have any members then it isn't included in results.
Schema is as follows:
Contact is a table of contacts
ContactList is a table of contactlists
ContactListContact is a junction table that marries the 2.
ContactID is a FK in ContactListContact
ContactListID is a FK in ContactListContact
How can I ensure that lists get included in the results even if they have no members.