Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy:
SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' On 10/3/2010 4:38 PM, Rick Faircloth wrote: > It ran without error, but the results returned > were wrong. > > There are 7,768 records in properties and > 7,758 records in properties_copy. (because I deleted > 10 records to have differences for results to show) > > The query below actually returns 9,999 records. > > The result set should only be the 10 missing records > present in properties_copy that are not in properties. > > > > -----Original Message----- > From: Jason Fisher [mailto:ja...@wanax.com] > Sent: Sunday, October 03, 2010 3:39 PM > To: cf-talk > Subject: Re: Can't figure out a query to accomplish this... > > > Can't you just do this? > > SELECT p.mls_number > FROM properties p LEFT OUTER JOIN > properties_copy pc ON p.mls = pc.mls > WHERE pc.mls IS NULL > AND p.mls = 'hmls' (wouldn't need this bit unless you really only > want the 'hmls' records) > > > > On 10/3/2010 3:12 PM, Rick Faircloth wrote: >> select p.mls_number >> from properties p >> where p.mls = 'hmls' >> and p.mls_number not in (select pc.mls_number from properties_copy pc >> where pc.mls = 'hmls') > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337812 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm