Ah, I see the issue. You just need to eliminate the other users before
joining. Something like (syntax may be off a little as I do not have SQL on
this machine to test it)...
Justin
SELECT I.ItemID, I.Name, I.LastModified,
V.UserID, V.LastViewedDate
FROM (Select ItemID, Name, LastModified from items where userid =
1) I LEFT OUTER JOIN Viewed V
ON I.ItemID = V.ItemID
WHERE V.LastViewedDate < I.LastModified
or V.UserID is null
ORDER BY I.ItemID
> -----Original Message-----
> From: Justin Greene [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 02, 2002 12:23 AM
> To: CF-Talk
> Subject: RE: SQL query driving me nuts
> What about something like this. (note the placement of the
> () with the
> AND/OR)...
>
> Justin
>
> 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)
> or Viewed.UserID is null
> ORDER BY Items.ItemID
>
>
>
> > -----Original Message-----
> > From: Steven Duff [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, August 02, 2002 12:00 AM
> > To: CF-Talk
> > Subject: SQL query driving me nuts
> >
> > <SNIP>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
