With MySQL? I couldn't find anything about "minus" in the MySQL docs, except referencing arithmetic functionality.
When I tried this: select p.mls_number from properties p minus select pc.mls_number from properties_copy pc I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'minus select pc.mls_number from properties_copy pc' at line 3 I tried it like this: select mls_number from properties minus select mls_number from properties_copy and got this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select mls_number from properties_copy' at line 4 -----Original Message----- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Sunday, October 03, 2010 4:31 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... I've always done select a, b, c from tablea minus select a, b, c from tableb pretty simple as long as the columns match On Sun, Oct 3, 2010 at 2:49 PM, Michael Grant <mgr...@modus.bz> wrote: > > 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:337804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
