WHERE NOT EXISTS should also work. On Tue, Aug 25, 2009 at 5:27 AM, Mark Henderson <shadefro...@gmail.com>wrote:
> > Greetings from the chilly south, > > I have this query and it returns the expected result set, but I can't > work out how to use a join instead of the NOT IN clause and I *know* > that is going to be more efficient. Basically, I want to exclude the > current special from the result set. Any ideas? > > <cfquery name="qGetRecentRecord" datasource="#request.dsn#"> > SELECT > TOP 1 > RV.ID AS RecID > ,RV.Date_Viewed > ,FS.ID > ,FS.Category_ID > ,FS.Title > ,FS.Comment > ,FS.Thumbnail > ,FS.Photo > ,FS.Price > ,FS.Year > ,FS.Date_Modified > ,FS.Active > ,C.Name As Category_Name > ,C.Image_Path > ,C.Image_Dir > FROM > tbl_ForSaleCategories C > INNER JOIN ( > tbl_RecentlyViewed RV > INNER JOIN > tbl_ForSale FS > ON RV.ID = FS.ID > ) > ON C.ID = FS.Category_ID > WHERE RV.ID NOT IN ( > SELECT ID > FROM tbl_CoverSpecial > ) > AND FS.Active = 1 > ORDER BY Date_Viewed ASC; > </cfquery> > > All help appreciated. TIA > > > adieu > Mark > -------*/ > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325706 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
