I've been working on this for a couple of days now, and just can't get
this to function in one query. Running MSDE from Office XP, so that's
SQL Server 2000, Enterprise Manager from the demo, on Win 2K Pro.
The situation simplified:
Table of Items:
ItemID int identity
ItemName varchar 30
itemLastModified smalldatetime
Sample Data
1 Item A 7/20/02
2 Item B 7/25/02
3 Item C 7/23/02
4 Item D 7/22/02
5 Item E 8/01/02
Table of Users:
UserID int identity
UserName varchar 30
Sample Data
1 Ralph
2 Ed
Each time an item is viewed by a user, an entry is made in the Viewed
table:
ViewedID int identity
ItemID int
UserID int
LastViewedDate smalldatetime
Sample Data
1 1 1 7/24/02
2 2 1 7/24/02
3 3 1 7/24/02
4 3 2 7/28/02
5 4 2 7/28/02
(IE, Ralph has viewed items 1,2 and 3, while Ed has looked at items 3
and 4. Item 2 has been modified since Ralph last looked at it, and
therefore it should show up as new to him the next time. Item 5 is
brand new and was created after their last visits, so neither has seen
it, and therefore there is no reference to it anywhere in this Viewed
table.
The idea is to generate the list of items that are new to a given
user, let's say Ralph. For Ralph, this should be 2,4, and 5. This is
where I'm having my problems.
My first thought is to generate a list of all items. I quickly see
that I need a left outer join to get all the items, as otherwise Item
5 is left off the list, since there is no corresponding item in the
Viewed table:
SELECT Items.ItemID, Items.Name, Items.LastModified,
Viewed.UserID, Viewed.LastViewedDate
FROM Items LEFT OUTER JOIN Viewed
ON Items.ItemID = Viewed.ItemID
ORDER BY Items.ItemID
This gives me:
Item Name Modified User Viewed
============================================
1 Item A 7/20/02 1 7/24/02
2 Item B 7/25/02 1 7/24/02
3 Item C 7/23/02 1 7/24/02
3 Item C 7/23/02 2 7/28/02
4 Item D 7/22/02 2 7/28/02
5 Item E 8/01/02 null null
Ok, all the needed info is there, now to whittle the list down. I need
to end up with Item A and C removed from the list. First, a quick
WHERE is added to the above query to check that it indeed matches the
two rows I eventually want removed:
SELECT Items.ItemID, Items.Name, Items.LastModified,
Viewed.UserID, Viewed.LastViewedDate
FROM Items LEFT OUTER JOIN Viewed
ON Items.ItemID = Viewed.ItemID
WHERE (Viewed.UserID = 1)
and (Viewed.LastViewedDate > Items.LastModified)
ORDER BY Items.ItemID
As expected, this gives me the two items Ralph has already seen:
Item Name Modified User Viewed
============================================
1 Item A 7/20/02 1 7/24/02
3 Item C 7/23/02 1 7/24/02
Now, my thinking is all I have to do is reverse the where clause,
getting all the items except for the two above.
SELECT Items.ItemID, Items.Name, Items.LastModified,
Viewed.UserID, Viewed.LastViewedDate
FROM Items LEFT OUTER JOIN Viewed
ON Items.ItemID = Viewed.ItemID
WHERE NOT( (Viewed.UserID = 1)
and (Viewed.LastViewedDate > Items.LastModified) )
ORDER BY Items.ItemID
However, this gives me:
Item Name Modified User Viewed
============================================
2 Item B 7/25/02 1 7/24/02
3 Item C 7/23/02 2 7/28/02
4 Item D 7/22/02 2 7/28/02
Item 5 is gone, and 3 is still in the list because User 2 Ed looked at
it. It's at this point that I'm stuck. No amount of tweaking the query
gets me just the rows I want:
Item Name Modified User Viewed
============================================
2 Item B 7/25/02 1 7/24/02
4 Item D 7/22/02 2 7/28/02
5 Item E 8/01/02 null null
All I really need from the query is the Item and the Name, so it
wouldn't really matter if User 2 was listed there. I guess it would be
more accurate if it said 4 null null like item 5.
I know I could solve the problem by generating two lists, the entire
item list, and the viewed list for Ralph, and then just skipping over
the items in the viewed list, but that just strikes me as sloppy. This
should be able to be done in one query, I believe.
Any help would be appreciated.
--
Steve Duff
ebay/bidville: ndsman
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
