Re: Can't figure out a query to accomplish this...
if you insist on using *, you can do SELECT p.* to select all columns from only one table in a join. but others have already said why selecting * (even from one table) may not be a very good idea. Azadi On 04/10/2010 21:59 , Rick Faircloth wrote: Yes, once I got the query working (except for the duplicate fields) I swapped to select * to avoid having to type the 50 or so fields involved. It works elsewhere, so I figured it should here. But as soon as I starting specifically naming the fields, the duplication cleared up. I would love to know why, but I guess that's just the way MySQL works with that particular query. Perhaps it's the way it has to be done with a left join. Anyway, all is well! Thanks, James! Rick -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: Monday, October 04, 2010 9:12 AM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Are you using select * to get the records? If so, get rid of that and name the select columns from p. select p.area, p.bedrooms, p.bathrooms from ... etc -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 4 October 2010 11:28, Rick Fairclothric...@whitestonemedia.com wrote: Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. �I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field ~| 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:338041 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
Are you using select * to get the records? If so, get rid of that and name the select columns from p. select p.area, p.bedrooms, p.bathrooms from ... etc -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 4 October 2010 11:28, Rick Faircloth ric...@whitestonemedia.com wrote: Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field ~| 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:337822 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
I tried the group by, but as I suspected, that only would work to eliminate duplicate *records*. What I was getting is duplicate *fields* within each record. However, James has the solution. See my reply to him... Thanks, Michael! Rick -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Monday, October 04, 2010 6:10 AM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Yes, distinct or a group by. This should work: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc where pc.mls_number is not null and pc.mls='hmls' ) and p.mls = 'hmls' and p.mls_number is not null And if not this should: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' GROUP BY mls_number, mls On Mon, Oct 4, 2010 at 12:29 AM, Andrew Scott andr...@andyscott.id.auwrote: You might need to provide the distinct, or adjust the join correctly. I am betting the distinct is needed. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 3:16 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Well, when I run this: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc ) and p.mls = 'hmls' and p.mls_number is not null I get no records returned... -Original Message- From: Andrew Scott [mailto:andr...@andyscott.id.au] Sent: Sunday, October 03, 2010 11:36 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 2:28 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~~ ~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf- talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~~ ~~~| 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:337819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
Yes, distinct or a group by. This should work: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc where pc.mls_number is not null and pc.mls='hmls' ) and p.mls = 'hmls' and p.mls_number is not null And if not this should: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' GROUP BY mls_number, mls On Mon, Oct 4, 2010 at 12:29 AM, Andrew Scott andr...@andyscott.id.auwrote: You might need to provide the distinct, or adjust the join correctly. I am betting the distinct is needed. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 3:16 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Well, when I run this: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc ) and p.mls = 'hmls' and p.mls_number is not null I get no records returned... -Original Message- From: Andrew Scott [mailto:andr...@andyscott.id.au] Sent: Sunday, October 03, 2010 11:36 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 2:28 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~~ ~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf- talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~~ ~~~| 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:337819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~| 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:337821 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk
RE: Can't figure out a query to accomplish this...
Yes, once I got the query working (except for the duplicate fields) I swapped to select * to avoid having to type the 50 or so fields involved. It works elsewhere, so I figured it should here. But as soon as I starting specifically naming the fields, the duplication cleared up. I would love to know why, but I guess that's just the way MySQL works with that particular query. Perhaps it's the way it has to be done with a left join. Anyway, all is well! Thanks, James! Rick -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: Monday, October 04, 2010 9:12 AM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Are you using select * to get the records? If so, get rid of that and name the select columns from p. select p.area, p.bedrooms, p.bathrooms from ... etc -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 4 October 2010 11:28, Rick Faircloth ric...@whitestonemedia.com wrote: Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field ~| 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:337824 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
I swapped to select * to avoid having to type the 50 or so fields involved. SOT: Point of note, regardless of how tedious this task may seem you should always select only the fields you are using. Unless you know that you are using all fields in a table and that the table columns will never change for that table, you should define the fields in your select statement. It's not only more streamlined, but it makes it easier for other devs to read and understand your code. my $0.02 ~| 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:337825 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
Naming all the columns also prevents issues with incorrect pooled statements being cached when you add a column to the DB. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 4 October 2010 22:14, Michael Grant mgr...@modus.bz wrote: SOT: Point of note, regardless of how tedious this task may seem you should always select only the fields you are using. Unless you know that you are using all fields in a table and that the table columns will never change for that table, you should define the fields in your select statement. It's not only more streamlined, but it makes it easier for other devs to read and understand your code. ~| 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:337826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
In any join query, when you select *, you'll get all columns from both tables you've joined. It doesn't matter if the columns are named the same; since they are in different tables they are providing different data and are returned as separate columns. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 4 October 2010 21:59, Rick Faircloth ric...@whitestonemedia.com wrote: Yes, once I got the query working (except for the duplicate fields) I swapped to select * to avoid having to type the 50 or so fields involved. It works elsewhere, so I figured it should here. But as soon as I starting specifically naming the fields, the duplication cleared up. I would love to know why, but I guess that's just the way MySQL works with that particular query. Perhaps it's the way it has to be done with a left joi ~| 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:337827 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Can't figure out a query to accomplish this...
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:337794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:337795 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
Show us the structure of the two tables. andy -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 2:12 PM To: cf-talk Subject: Can't figure out a query to accomplish this... 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:337796 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:337797 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
Can't you just do this? SELECT p.mls_number FROM properties p LEFT OUTER JOIN properties_copy pc ON p.mls = pc.mls WHERE pc.mls IS NULL AND p.mls = 'hmls' (wouldn't need this bit unless you really only want the 'hmls' records) On 10/3/2010 3:12 PM, Rick Faircloth wrote: select p.mls_number from properties p wherep.mls = 'hmls' and p.mls_number not in (select pc.mls_number from properties_copy pc where pc.mls = 'hmls') ~| 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:337798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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.comwrote: 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:337799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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.comwrote: 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:337800 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
It ran without error, but the results returned were wrong. There are 7,768 records in properties and 7,758 records in properties_copy. (because I deleted 10 records to have differences for results to show) The query below actually returns 9,999 records. The result set should only be the 10 missing records present in properties_copy that are not in properties. -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 3:39 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Can't you just do this? SELECT p.mls_number FROM properties p LEFT OUTER JOIN properties_copy pc ON p.mls = pc.mls WHERE pc.mls IS NULL AND p.mls = 'hmls' (wouldn't need this bit unless you really only want the 'hmls' records) On 10/3/2010 3:12 PM, Rick Faircloth wrote: select p.mls_number from properties p wherep.mls = 'hmls' and p.mls_number not in (select pc.mls_number from properties_copy pc where pc.mls = 'hmls') ~| 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:337801 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:337802 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
I don't see why either. This returns no results: select p.mls_number from properties p where p.mls = 'hmls' andp.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.comwrote: 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
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 fromproperties p minus select pc.mls_number fromproperties_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 fromproperties minus select mls_number fromproperties_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.comwrote: 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
Re: Can't figure out a query to accomplish this...
On Sun, Oct 3, 2010 at 12:12 PM, Rick Faircloth ric...@whitestonemedia.com wrote: 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? It looks right but try this: 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 = ~| 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:337805 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Can't figure out a query to accomplish this...
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.comwrote: 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 fromproperties p minus select pc.mls_number fromproperties_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 fromproperties minus select mls_number fromproperties_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.comwrote: 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
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 fromproperties p minus select pc.mls_number fromproperties_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 fromproperties minus select mls_number fromproperties_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.comwrote: 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:337807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
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 fromproperties 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
RE: Can't figure out a query to accomplish this...
This ran without error: select p.mls_number fromproperties p where (p.mls = 'hmls') and (p.mls_number not in ( select pc.mls_number from properties_copy pc where pc.mls = 'hmls' )) But, again, there's 0 records in the result set. -Original Message- From: Maureen [mailto:mamamaur...@gmail.com] Sent: Sunday, October 03, 2010 4:56 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... On Sun, Oct 3, 2010 at 12:12 PM, Rick Faircloth ric...@whitestonemedia.com wrote: 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? It looks right but try this: 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 = ~| 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:337809 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
That worked. I got a record set of 1, which is correct. But when I changed the sql to: select mls_number from properties where mls_number not in (select mls_number from properties_copy) I get nothing in the result set... ??? -Original Message- From: Michael Grant [mailto:mgr...@modus.bz] Sent: Sunday, October 03, 2010 4:58 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... 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.comwrote: 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 fromproperties p minus select pc.mls_number fromproperties_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 fromproperties minus select mls_number fromproperties_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.comwrote: 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:337810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
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 fromproperties p minus select pc.mls_number fromproperties_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 fromproperties minus select mls_number fromproperties_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.comwrote: 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
Re: Can't figure out a query to accomplish this...
Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' On 10/3/2010 4:38 PM, Rick Faircloth wrote: It ran without error, but the results returned were wrong. There are 7,768 records in properties and 7,758 records in properties_copy. (because I deleted 10 records to have differences for results to show) The query below actually returns 9,999 records. The result set should only be the 10 missing records present in properties_copy that are not in properties. -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 3:39 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Can't you just do this? SELECT p.mls_number FROM properties p LEFT OUTER JOIN properties_copy pc ON p.mls = pc.mls WHERE pc.mls IS NULL AND p.mls = 'hmls' (wouldn't need this bit unless you really only want the 'hmls' records) On 10/3/2010 3:12 PM, Rick Faircloth wrote: 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') ~| 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:337812 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~| 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:337816 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 2:28 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~~ ~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~| 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:337818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
Well, when I run this: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc ) and p.mls = 'hmls' and p.mls_number is not null I get no records returned... -Original Message- From: Andrew Scott [mailto:andr...@andyscott.id.au] Sent: Sunday, October 03, 2010 11:36 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 2:28 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~~ ~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~| 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:337819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Can't figure out a query to accomplish this...
You might need to provide the distinct, or adjust the join correctly. I am betting the distinct is needed. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 3:16 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Well, when I run this: select * fromproperties p where p.mls_number not in ( select pc.mls_number from properties_copy pc ) and p.mls = 'hmls' and p.mls_number is not null I get no records returned... -Original Message- From: Andrew Scott [mailto:andr...@andyscott.id.au] Sent: Sunday, October 03, 2010 11:36 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... I was wondering why you are doing an left join and not a sub select, the join will create duplicates if not the right way around. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Monday, 4 October 2010 2:28 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Spoke (wrote) too soon... I'm getting the correct records, but I just realized I'm getting two of every field returned. I tried other joins, but can't affect the fields so that I get just one field. How do I modify the query to return just one field? I'm getting: (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc... When it should be: (record 1) area bedrooms bathrooms, etc... ??? -Original Message- From: Rick Faircloth [mailto:ric...@whitestonemedia.com] Sent: Sunday, October 03, 2010 11:17 PM To: cf-talk Subject: RE: Can't figure out a query to accomplish this... Ok...here's the final solution. (Had to watch some football before I could sort it out :o) select p.mls_number from properties p left joinproperties_copy pc on pc.mls_number = p.mls_number wherepc.mls_number is null and p.mls = 'hmls' and p.mls_number is not null That last line had to be added because there were some records with no mls_number that I didn't know about earlier. I swear it seems like this was one of the first queries I tried. But, whatever, it works! Thanks for the help, everyone! Rick -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: Sunday, October 03, 2010 7:07 PM To: cf-talk Subject: Re: Can't figure out a query to accomplish this... Ah, then reverse the JOIN (unless MySQL handles this differently) if you want only the ones IN _copy: SELECT pc.mls_number FROM properties_copy pc LEFT OUTER JOIN properties p ON pc.mls = p.mls WHERE p.mls IS NULL AND pc.mls = 'hmls' ~~ ~~~| 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:337817 Subscription: http://www.houseoffusion.com/groups/cf- talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~~ ~~~| 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:337819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~| 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:337820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
