Thanks Billy and Michael (and Kevin). After some trial and error I
managed to stumble upon solution (the outer join gave me results but
not the expected set).
<cfquery name="qGetRecentRecord" datasource="#request.dsn#">
SELECT TOP 1
RV.ID AS Rec_ID
,RV.Date_Viewed
,FS.ID
,FS.Category_ID
,FS.Title
,FS.Comment
,FS.Thumbnail
,FS.Photo
,FS.Price
,FS.Year
,FS.Date_Modified
,C.Name As Category_Name
,C.Image_Path
,C.Image_Dir
FROM
tbl_RecentlyViewed RV
INNER JOIN
(
tbl_ForSaleCategories C
INNER JOIN
(
tbl_ForSale FS
LEFT JOIN
tbl_CoverSpecial CS
ON FS.ID = CS.ID
)
ON C.ID = FS.Category_ID
)
ON RV.ID = FS.ID
WHERE FS.Active = 1
AND CS.ID IS NULL
</cfquery>
It can probably be improved but it works so I'm satisfied :-)
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:325705
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
