: 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
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
[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
...
-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
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
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
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
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
Does MySQL support 'minus'?
On Oct 3, 2010 2:12 PM, Rick Faircloth ric...@whitestonemedia.com
wrote:
~|
Order the Adobe Coldfusion Anthology now!
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
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
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
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
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
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
.
-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
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
, 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
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')
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
...@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
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
' ))
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
...@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
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
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
-
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
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
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
[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
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
31 matches
Mail list logo
