Try this:
...
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
LEFT JOIN
tbl_CoverSpecial CS
ON RV.ID = CS.ID
WHERE CS.ID IS NULL
AND FS.Active = 1
ORDER BY Date_Viewed ASC;
Mark Henderson 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:325678
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
