Looong list of things going on, but try this query to see what you get! SELECT i.ItemName FROM Users u, LastViewed lv, Items i WHERE i.ItemId = lv.ItemId AND i.itemLastModified > lv.LastViewedDate AND lv.UserID = '1'
Paul Giesenhagen QuillDesign ----- Original Message ----- From: "Steven Duff" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, August 01, 2002 10:59 PM Subject: SQL query driving me nuts > 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
