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:[email protected]]
> 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
