Well, I've actually already tried Carsten's proposed MySQL alternatives to MINUS:
The first is to use a subselect: select mls_number from properties where mls_number not in (select mls_number from properties_copy) That returns 0 records. His second proposed solution is a left join, which I tried first in a long string of sql attempts: select p.mls_number from properties p left join properties_copy pc using mls_number where pc.mls_number is null That sql returns 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 'mls_number where pc.mls_number is 'null'' at line 4 My data should be correct. I'm simply taking the first table, 'properties', and copying the structure and data into 'properties_copy', then deleting 10 records from 'properties_copy' in order to generate a result set of 10 records...the 10 that are in 'properties', but not in 'properties_copy'. And the mls_numbers are unique. -----Original Message----- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 4:40 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... After reading through the article I got the impression that the author was trying to show how to write queries in MySQL syntax to return the same results as intersect and minus...(I tried both intersect and minus and they threw MySQL syntax errors) Going to try the alternative he proposed now. -----Original Message----- From: andy matthews [mailto:li...@commadelimited.com] Sent: Sunday, October 03, 2010 3:22 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Appears that MySQL does support intersects, or minus like Greg suggested. http://www.bitbybit.dk/carsten/blog/?p=71 andy -----Original Message----- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Sunday, October 03, 2010 2:18 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Does MySQL support 'minus'? On Oct 3, 2010 2:12 PM, "Rick Faircloth" <ric...@whitestonemedia.com> wrote: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:337808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
