Try using an OUTER JOIN and specify the criteria in the JOIN statement
...
FROM table1
LEFT OUTER JOIN table2
ON table1.col1 = table2.col1
AND {criteria goes here}
...
Thanks,
Mike
-----Original Message-----
From: Mark Henderson [mailto:shadefro...@gmail.com]
Sent: Tuesday, August 25, 2009 4:27 AM
To: cf-talk
Subject: SQL Help
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:325673
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
