Something like the following usually works... I prefer these join
methods, rather than the commonly used subquery "not in" method.
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
LEFT JOIN ddMemberReviews r
ON r.dogID = a.dogID
WHERE a.memberID = #member.memberID#
AND DATEADD("n",30,a.auctionTime) <= getDate() and r.dogID is null
-----Original Message-----
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 22, 2003 10:33 AM
To: SQL
Subject: Showing Non existing records
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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
