Complicated mysql query

[benn600]

I have a site, http://giftpathways.com which tracks user's wish lists (mainly for Christmas lists at this time).  It keeps track of wish lists based on group, wish list items, and comments on those wish list items.

 

With my actively used group, we have 13 users with around 150 wish list items/comments.  It quickly grew beyond the scope of checking every page for changes.  I need a way to list unread items.  Specifically, I have three in mind: new members to the group (therefore, new wish lists), new wish list items, and new comments.

 

I was thinking of the best way to do this and it seems like the most accurate way is to create a new table that lists every item that was read.  So, if the item is not found, it has not been read.  The site is still very small but I'm not sure if this could quickly grow exponentially and be a bad choice as it would mean tons and tons of records.  One for every item times the number of members.

 

Anyway, I need help getting the mysql code down.  How can I compare two tables: Tables "groups" and "pageViews" track this.  I'm using an ID on pageViews to identify if the item is a groupmember, wishlist item, or comment.  Then, the ID # refers to that type and the respective table is used.

 

Group members are identification number 0.  So I need all items from "groups" returned where an entry DOES NOT exist in "pageViews" ... this is a tricky one, I think!

 

Once that new group member's page is visited, the value could be inserted.

 

As a side note, I already have a counter tracking every page view.  It probably already has sufficient information to do this!  In fact, it really does!  So users wouldn't have to start at having read nothing.  Plus, that would mean no additional table.  But I still have to solve the problem.  Any ideas?  Left and right join don't seem to help.

[fenway]

Why not store the date of the last read?