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-t
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:and
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
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
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.ml
CF does not support transparent gif's out of the box. But you can create
transparent png's using the ImageNew() function, and imageType "argb". Then use
ImageDrawText to draw whatever text you want onto the image.
You can't, by default the cfimage will only work in RGB and for what you
want you need aRGB.
The only way around this is to look into using the java solution, of
creating the image buffer and doing what you need to do. There are plenty of
examples on the net on how to use Java to do this, or you
Can someone show me an example of using cfimage to create a png or gif of
text from a variable where the image has a transparent background?
Terry
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfu
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:
>
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 mlsN
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...@mo
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
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 jo
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:
s
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 res
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
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 SQ
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 M
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.
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 miss
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 query to accomp
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
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
27 matches
Mail list logo
