To trouble shoot why not grab a mls number from one table that you know exists in the other and try this a few times with different ones.
select mls_number from properties where mls_number = ( select mls_number from properties_copy where mls_number = 'your mls from properties' ) If you get a result then you know at least that theoretically a select not in should work as expected. On Sun, Oct 3, 2010 at 4:50 PM, Rick Faircloth <ric...@whitestonemedia.com>wrote: > > 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:337806 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
