lto: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.
>
>
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
htt
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 wrote:
> SOT: Point of note, regardless of how tedious this ta
>
> 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 n
, 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-Secur
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...@a
[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_co
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 wrote:
>
> Spoke (wrote) too
> 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
drew 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,
An
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
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
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:
ds.
>
> 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
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 support
...@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 prope
ls = '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,
ailto: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 sa
aircloth [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
t;
> 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-
&
On Sun, Oct 3, 2010 at 12:12 PM, Rick Faircloth
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 suggesti
o: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, Mic
elects...
-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 reco
.
-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.bitbyb
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 thi
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 wrote:
>
> I really don't see why your example won't work.
> It should be selecting the records from properties that don't appea
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 Faircl
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
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 que
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
Does MySQL support 'minus'?
On Oct 3, 2010 2:12 PM, "Rick Faircloth"
wrote:
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archiv
31 matches
Mail list logo
