WHERE (Viewed.UserID = 1)
and (Viewed.LastViewedDate < Items.LastModified)
or (Viewed.LastViewedDate IS NULL)
How about that?
> -----Original Message-----
> From: Steven Duff [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 2 August 2002 4:00 p.m.
> To: CF-Talk
> 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
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
