I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around.
Regards, Andrew Scott http://www.andyscott.id.au/ > -----Original Message----- > From: Rick Faircloth [mailto:ric...@whitestonemedia.com] > Sent: Monday, 4 October 2010 2:28 PM > To: cf-talk > Subject: RE: Can't figure out a query to accomplish this... > > > 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:ric...@whitestonemedia.com] > 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:ja...@wanax.com] > 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:337818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
