alphacooler
Programmer
Here is the layout.
I have the following 5 tables: users, groups, groups_members, groups_messages, groups_articles. I am trying to run a query that will count all new messages and articles from groups that a particular user belongs to and GROUP BY groups.group_name. The end result would look something like: "Group_Name 3 New Messages & 5 New Comments"
Here is what I am using:
SELECT groups.group_name, COUNT(DISTINCT groups_articles.articleID) AS num_new_articles, COUNT(DISTINCT groups_messages.messageID) AS num_new_messages
FROM groups_articles
INNER JOIN groups ON ( groups_articles.groupID = groups.groupID )
inner join groups_members ON ( groups.groupID = groups_members.groupID )
left join groups_messages ON ( groups.groupID = groups_messages.groupID )
WHERE (groups_articles.date_added >[insert integer for date] OR
groups_messages.date_added >[insert integer for date]) AND groups_members.userID=[insert userID]
GROUP BY groups.groupID
The problem is that when a group has either new messages or new articles posted to it, but not both at the same time the query will come up with a COUNT that shows ALL the messages/recipes even though there are no new ones.
Any ideas?
I have the following 5 tables: users, groups, groups_members, groups_messages, groups_articles. I am trying to run a query that will count all new messages and articles from groups that a particular user belongs to and GROUP BY groups.group_name. The end result would look something like: "Group_Name 3 New Messages & 5 New Comments"
Here is what I am using:
SELECT groups.group_name, COUNT(DISTINCT groups_articles.articleID) AS num_new_articles, COUNT(DISTINCT groups_messages.messageID) AS num_new_messages
FROM groups_articles
INNER JOIN groups ON ( groups_articles.groupID = groups.groupID )
inner join groups_members ON ( groups.groupID = groups_members.groupID )
left join groups_messages ON ( groups.groupID = groups_messages.groupID )
WHERE (groups_articles.date_added >[insert integer for date] OR
groups_messages.date_added >[insert integer for date]) AND groups_members.userID=[insert userID]
GROUP BY groups.groupID
The problem is that when a group has either new messages or new articles posted to it, but not both at the same time the query will come up with a COUNT that shows ALL the messages/recipes even though there are no new ones.
Any ideas?