Customers and Notifications
Customers consists of
Notifications consist of
Fields are Text unless otherwise noted (not that I think it matters with this query
Anyway. I have the following SELECT statement that works.
SELECT Customers.CustomerName, Customers.ListID, Count(Notifications.NotificationText) AS TotalMsg FROM Customers LEFT JOIN Notifications ON Customers.ListID = Notifications.CustomerID GROUP BY Customers.CustomerName, Customers.ListID;
This produces all customers and displays their total message counts whether it's 0 or not. However here is my problem.
I don't want to count anything where Deleted = True in the Notifications table.
I thought I could just add a WHERE Deleted = False to the above query but that only returns customers who have at least one row in the Notifications table.
I then thought I could just add the Deleted column to the list of Fields and to the Group By but I'm right back to only customers that have rows in the Notification table show up. Now I feel like I may need to do a sub-select of the notifications table without WHERE Deleted = False but I can't seem to get it to work
Here is what I've tried and I won't pretend that I even fully understand what I'm doing with it but it produces the same results as the original Query above.
SELECT Customers.CustomerName, Customers.ListID, Count(Notifications.NotificationText) AS TotalMsg FROM Customers LEFT JOIN Notifications ON Customers.ListID = Notifications.CustomerID WHERE EXISTS (SELECT * FROM Notifications WHERE Deleted = False) GROUP BY Customers.CustomerName, Customers.ListID
Any help is greatly appreciated.
This post has been edited by CharlieMay: 04 March 2011 - 09:58 AM