I tried the group by, but as I suspected, that only would work to eliminate duplicate *records*. What I was getting is duplicate *fields* within each record.
However, James has the solution. See my reply to him... Thanks, Michael! Rick -----Original Message----- From: Michael Grant [mailto:[email protected]] Sent: Monday, October 04, 2010 6:10 AM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Yes, distinct or a group by. This should work: select * from properties p where p.mls_number not in ( select pc.mls_number from properties_copy pc where pc.mls_number is not null and pc.mls='hmls' ) and p.mls = 'hmls' and p.mls_number is not null And if not this should: 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' GROUP BY mls_number, mls On Mon, Oct 4, 2010 at 12:29 AM, Andrew Scott <[email protected]>wrote: > > 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:[email protected]] > > 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:[email protected]] > > 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:[email protected]] > > > 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:[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 > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > ~~~~~~~~~~~| > > 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:337823 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
