Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem using operators

Status
Not open for further replies.

alphacooler

Programmer
Aug 29, 2005
73
US
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top