Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field.
How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -----Original Message----- From: Rick Faircloth [mailto:[email protected]] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left join properties_copy pc on pc.mls_number = p.mls_number where pc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -----Original Message----- From: Jason Fisher [mailto:[email protected]] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... 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' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
