You might need to provide the distinct, or adjust the join correctly. I am betting the distinct is needed.
Regards, Andrew Scott http://www.andyscott.id.au/ > -----Original Message----- > From: Rick Faircloth [mailto:ric...@whitestonemedia.com] > Sent: Monday, 4 October 2010 3:16 PM > To: cf-talk > Subject: RE: Can't figure out a query to accomplish this... > > > Well, when I run this: > > select * > from properties p > where p.mls_number not in ( select pc.mls_number from > properties_copy pc ) > and p.mls = 'hmls' > and p.mls_number is not null > > I get no records returned... > > > > -----Original Message----- > From: Andrew Scott [mailto:andr...@andyscott.id.au] > Sent: Sunday, October 03, 2010 11:36 PM > To: cf-talk > Subject: RE: Can't figure out a query to accomplish this... > > > 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:337819 > 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:337820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
