I decided to set up two new tables in the database to make sure my data wasn't the problem.
It appears the data *is* the problem. When I ran a typical subselect select mlsNumber from mlsTable1 where mlsNumber not in (select mlsNumber from mlsTable2) I got the 2 records with mlsNumber's from mlsTable1 that are not in mlsTable2. When I tried the query this way: select mlsNumber from mlsTable1 minus select mlsNumber from mlsTable2 I got a MySQL syntax error again. But, I still don't see how my data could be the problem. I simply duplicated the 'properties' table as 'properties_copy' and then deleted 10 records from the end of 'properties_copy' to create a difference of 10 records. But I'll have to focus on the data issue. I know that I had used almost all of the variations of queries I had tried before and they worked, so I knew they should be working. Thanks for the input, everyone! -----Original Message----- From: Ben Forta [mailto:b...@forta.com] Sent: Sunday, October 03, 2010 5:03 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... SELECT MINUS is indeed supported by MySQL 4.1 and later. But, basically it is just an alternative for a subquery with a NOT IN. (Internal processing is actually different, and the subquery option may perform worse with larger data sets). So, the following 2 statements should do the same thing: select a, b, c from tablea minus select a, b, c from tableb select a, b, c from tablea where a, b, c not in (select a, b, c from tableb) In other words, you don't actually need SELECT MINUS. If you can't get it to work, use the subquery. And it looks like you tried just that in the first place. So I'd go back and just execute the subquery to make sure it returns what you expect. Then, if it does, try your outer query with a hardcoded list, to make sure it behaves as you'd expect. Then try it all put together again. --- Ben -----Original Message----- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 4:51 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... 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:337811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
