I have the following query:
SELECT a.dogID, d.name, d.dogID
FROM ddAuctions a
INNER JOIN ddMembers m
ON m.memberID = a.memberID
INNER JOIN ddDogs d
ON d.dogID = a.dogID
RIGHT OUTER JOIN ddMemberReviews r
ON r.dogID NOT IN (a.dogID)
WHERE a.memberID = #member.memberID#
AND DATEADD("n",30,a.auctionTime) <= getDate()
What I am trying to accomplish, is to find all the dogID's found in the ddAuctions
that have the #member.memberID# but are NOT found in the ddMemberReviews table.
(This is to show the MEMBER (in the ddAuctions table) what auctions they are listed in
and have not yet posted a REVIEW (in the ddMemberReviews table)
Here is a sniplet of the tables.
ddAuctions
-----------------------------
memberID, dogID, auctionTime
ddMembers
------------------------------
memberID, name
ddDogs
-----------------------------
dogID, name
ddMemberReviews
-----------------------------
memberID, dogID, shortComment
Thank you,
Paul Giesenhagen
QuillDesign
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
