Tim,
Thank you, that worked perfectly ....
Paul Giesenhagen
QuillDesign
----- Original Message -----
From: "Raster, Tim" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 10:44 AM
Subject: RE: Showing Non existing records
> 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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
