I don't see why either. This returns no results: 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' )
Since 4.1 (or so), MySQL has supported subselects... -----Original Message----- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Sunday, October 03, 2010 3:50 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... I really don't see why your example won't work. It should be selecting the records from properties that don't appear in properties_copy and has 'hmls' as the mls value. Is this not giving you the results you expect or do you just not want a sub select? On Sun, Oct 3, 2010 at 3:12 PM, Rick Faircloth <ric...@whitestonemedia.com>wrote: > > I'm using MySQL 5. > > I want to compare table1 to table2 and get any > records in table1 that don't exist in table2. > > I have tried everything I could think of and that > I could find on the 'net. > > Nothing's working. > > I've tried > > - select where not in (subselect) > - select where not exists (subselect) > - from dual > - left join where null > - blah, blah, blah (this one really performed badly) > > How can I write this to make it work? > > 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') > > Any kind suggestions for a weary soul? > > Thanks, > > Rick > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:337803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
