Ah. Ad-hoc. Gotcha.
(Can you tell I have been tuning monster sql queries all day? Here is to a sql
free weekend! ;-)
-Leigh
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/143
show them
why photos were appearing with the wrong properties.
Thanks for your help!
Rick
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Friday, August 12, 2011 4:05 PM
To: cf-talk
Subject: RE: How to write this MySQL query...
Rick - If you do not need the
Rick - If you do not need the "TimesUsed" count, you might also try the WHERE
EXISTS approach to compare performance.
-Leigh
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430
Thanks, guys...
I decided to try Leigh's suggestion first, and it worked.
Thanks for the suggestions, everyone!
Rick :o)
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Friday, August 12, 2011 2:02 PM
To: cf-talk
Subject: Re: How to write this MySQL
> - find all propertyID's from a table that had duplicate
> photo200 field values
If you mean _different_ propertyID's sharing the same photo200 value, you would
need to count by the photo200 column instead. Then match it with the
propertyID.
One option is using a join. Another would be a sub
I'd think grouping by propertyID as well would be a good
startotherwise you're just getting ALL duplicate photo200 values
across ALL properties.
What is wrong with your results? that may help us help you Rick ;-)
A brute force way is to:
select propertyID, count(photo200) as NumPhoto200
Doesn't the count have to be count(*) but otherwise it looks fine to me.
On Sat, Aug 13, 2011 at 3:44 AM, Rick Faircloth wrote:
>
> Hi, guys...
>
> I've been writing, research, and re-writing a query
> over and over trying to get the correct (ie, trustworthy)
> results, but can't make it work.
Hi, guys...
I've been writing, research, and re-writing a query
over and over trying to get the correct (ie, trustworthy)
results, but can't make it work.
How would I write a query that would:
- find all propertyID's from a table that had duplicate photo200 field
values
I've tried every variat
ah yes there were a few extra lines
AND (visaudactweight.AuditorID = AuditorNames.AuditorID))
AND (visaudactweight.CompanyID = CompanyNames.CompanyID))
AND (visaudactweight.GKprRecID = GateKeeperRecs.GKprRecID))
AND (GateKeeperRecs.GKeeperID = GKeeperNames.GKeeperID))
but its the AS MATTOTA
>You can not use a column alias you define in the select list as a base
>column to some other calculated column in that same select list, i.e.:
True.
>SELECT A + B AS C, C / D AS E
I have to say I'm surprised Access actually _does_ allow you to do that. I'd
never tried it before now be
Well unless there is more to the where clause that you didn't paste, all
those extra parentheses are going to cause an error, in addition to the fact
that you're not joining any of the other tables.
On 10/20/07, Toby King <[EMAIL PROTECTED]> wrote:
>
> Hi there
>
> I have a query that I have creat
Peter,
The reason it's not working is not because of the alias, but because
you are trying to use the alias in the SELECT clause. MySQL only
allows you to reference aliases in the GROUP BY, ORDER BY, or HAVING
clauses. (http://dev.mysql.com/doc/refman/5.0/en/problems-with-
alias.html)
You
>Hi there
>
>I have a query that I have created - it works against a MS-Access database
>but does not work with MySQL. I have searched through the MySQL notes that I
>have but cant find out why - I think its because of the AS MatTotal section
>of the query.
What do you mean by "does not work with
Peter Tanswell wrote:
>
> I have a query that I have created - it works against a MS-Access database
> but does not work with MySQL. I have searched through the MySQL notes that I
> have but cant find out why - I think its because of the AS MatTotal section
> of the query.
You can not use a colum
Hi there
I have a query that I have created - it works against a MS-Access database but
does not work with MySQL. I have searched through the MySQL notes that I have
but cant find out why - I think its because of the AS MatTotal section of the
query.
Thanks in advance for feedback
SELECT vi
Hi there
I have a query that I have created - it works against a MS-Access database
but does not work with MySQL. I have searched through the MySQL notes that I
have but cant find out why - I think its because of the AS MatTotal section
of the query.
Thanks in advance for feedback
SELECT visaud
I like to sit back, and wonder: how many apps are out there, running
about 20-80% slower than they should be... =]
On 7/14/07, Hansjoerg <[EMAIL PROTECTED]> wrote:
> Hello!
>
> Incredible - going back to this old version resolved the performance issue
> ... very, very strange ;-)
>
> Best regard
Hello!
Incredible - going back to this old version resolved the performance issue ...
very, very strange ;-)
Best regards,
Hansjoerg
>> I have got two simple SQL statements in my CFMX - application:
>
>I'm going to take a wild stab and say it's the version of the mysql
>driver.
>
>Try using m
On 7/14/07, peter mayer wrote:
> Hello!
>
> I have got two simple SQL statements in my CFMX - application:
I'm going to take a wild stab and say it's the version of the mysql
driver.
Try using mysql-connector-java-3.0.17-ga instead.
Or perhaps whatever driver cf8 ships with... I don't think I sa
Hello!
I have got two simple SQL statements in my CFMX - application:
SELECT
companyname,
uidnumber,
description,
telephone
FROM
companies
WHERE
entrykey = ?
;
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 2405961E-FE08-E3BE-059C648DC01198A9
SELECT
LEFT(companyname, 250) AS companyn
I believe it would be...
UPDATE contributors,contributor_sort SET
contributors.namesort=contributor_sort.namesort
WHERE contributors.name=contributor_sort.name
--
Jim Wright
Wright Business Solutions
[EMAIL PROTECTED]
919-417-2257
off the top of my head, may have errors..
update contributors c
set c.namesort = ( select cs.namesort
from contributor_sort cs
where c.name = cs.name)
On 6/3/06, Jim McAtee <[EMAIL PROTECTED]> wrote:
> Using MySQL 5.0.21, I have two tables:
>
> contributors
> -
> contributor_id
>
Using MySQL 5.0.21, I have two tables:
contributors
-
contributor_id
name
namesort
contributor_sort
-
name
namesort
I'd like to update the namesort column in contributors by using the values
in the namesort column in contributor_sort wherever name = name. I don'
On Tuesday 28 March 2006 17:15, Les Mizzell wrote:
> `idc`.`personnel`.`county_id` is a LIST
>
> So I need to add a WHERE statement to the above like:
>
> WHERE
>`idc`.`locations`.`locID` IN `idc`.`personnel`.`county_id`
`idc`.`locations`.`locID` IN (`idc`.`personnel`.`county_id`)
?
TBH 'in'
Using a view in mySQL,
From the current query:
SELECT blah, blah...
from (`idc`.`locations`
join `idc`.`personnel`
on((`idc`.`personnel`.`county_id` = `idc`.`locations`.`locID`)))
`idc`.`personnel`.`county_id` is a LIST
So I need to add a WHERE statement to the above like:
WHERE
This is a really odd occurrence and I wonder if anyone has run into
themselves, or more importantly knows how to get around it.
I'm using ColdFusion 6.1 with mySQL 4.0. Here's my data structure:
eq_year - smallint(4)
mfr_name - varchar(60)
eq_model - varchar(12)
and the query:
SELECT CONCAT(' ',
[quote]Any ideas guys?[/quote]
yeah
he's what u do *
sorry this post has been censored ;)
-- Original Message --
From: Jamie Price <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Date: Thu, 16 Dec 2004 18:06:21 -0400
>Any ideas guys?
>
>
Any ideas guys?
~|
Special thanks to the CF Community Suite Silver Sponsor - RUWebby
http://www.ruwebby.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187958
Archives: http://www.houseoffusion.com/cf_lists/threads.c
Jochem - that code worked (with some minor adjustment)!
Thanks!
Now, to up the level of complexity a bit. currently it outputs like so:
userid1
cardid1
cardid2
.
.
.
userid2
cardid1
cardid2
In other words it lists the other users with matching cards and then below each
user it lists the cards
Hi jamie ... took a look
I know its a bit of a fudge but as SQL's letting you down cant you wrap the
bit that outputs the results in a
Not ideal I know
> Simon - Here's the dumps - the GetMatches dump has never returned
> results for me (though it seems like it is doing something
Jamie Price wrote:
>>
>> SELECT o.UserID, GROUP_CONCAT(o.CardID)
>> FROM mycards t INNER JOIN mycards o ON t.cardid = o.cardid
>> WHEREt.trade = 1
>> AND
>> o.own = 1
>> AND
>> t.UserID = > cfsqltype="cf_sql_integer">
>> GROUP BY o.UserID
>
> I tried r
OK Jamie
you could migrate to PostgreSQL? Do that...
Cheers
Marco
On Wed, 15 Dec 2004 09:46:18 -0400, Jamie Price <[EMAIL PROTECTED]> wrote:
> Simon - Here's the dumps - the GetMatches dump has never returned results for
> me (though it seems like it is doing something since it's outputting r
Simon - Here's the dumps - the GetMatches dump has never returned results for
me (though it seems like it is doing something since it's outputting results)
GetWantedCards Query - query
CARDID USERID
1 41001101 7
2 41001102 7
3 41001151 7
4 41001152 7
5 41001153 7
GetMatches Query - query
although its a little 'windy' can you cfdump the qGetWantedCards query and post
it ?
also the first and the last 'qGetMatches' cfdumped would be helpful too
Cheers
Simon
>GROUP_CONCAT works only for 4.1+ MySQL versions...
>http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
>
>
>Chee
GROUP_CONCAT works only for 4.1+ MySQL versions...
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
Cheers
Marco
On Tue, 14 Dec 2004 22:25:14 -0400, Jamie Price <[EMAIL PROTECTED]> wrote:
> >Jamie Price wrote:
> >
> >
> >SELECT o.UserID, GROUP_CONCAT(o.CardID)
> >FROM mycards t
>Jamie Price wrote:
>
>
>SELECT o.UserID, GROUP_CONCAT(o.CardID)
>FROM mycards t INNER JOIN mycards o ON t.cardid = o.cardid
>WHEREt.trade = 1
> AND
> o.own = 1
> AND
> t.UserID = cfsqltype="cf_sql_integer">
>GROUP BY o.UserID
>
>Jochem
I tried replaci
Jamie Price wrote:
>
> ...which is close, but no cigar. I need to get rid of those 'blank' entries,
> plus I need to arrange it so what's left comes out like this:
>
> Quote:
> UserID: 3
> Owns Cards: 41001152
>
> UserID: 8
> Owns Cards: 41001151
>
> UserID: 9
> Owns Cards: 41001151
>
Jamie
what you need?
Cheers
Marco
On Sat, 11 Dec 2004 21:25:39 -0400, Jamie Price <[EMAIL PROTECTED]> wrote:
> I know that this sounds like a pretty wild problem, but I would REALLY
> appreciate any help you guys could give - I've been putting my head through a
> wall for about two weeks try
I know that this sounds like a pretty wild problem, but I would REALLY
appreciate any help you guys could give - I've been putting my head through a
wall for about two weeks trying to get this function right. If you need
clarification on anything, just let me know. Thanks!
~~~
I'm working on a trading card site for an online card game. One of the key
functions of the site will be that a user can login and click on "match" and
the database will return a 'matching' trader - one that has all the cards the
user needs. I've setup a test version that counts on the user wit
That's what I was after. Thanks! Oh, and I am definately going to suggest
they make a FK with that ID in there.
- Original Message -
From: "Jochem van Dieten" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 07, 2
ROTECTED]>
Sent: Tuesday, December 07, 2004 10:36 AM
Subject: RE: MySQL query question
>I agree with Eric in the suggestion to normalize your structure (a single
> script/update statement could take care of updating your several months of
> records and make your life simpler in the fut
Todd wrote:
> In one table I have an ID as the primary key. In a second table, I have a
> string that is made out of that ID. I'm trying to match the two. The string
> looks like this: /folder1/folder2/some_other_stuff_that_changes/123.txt
> where 123 is the ID from the first table. How ca
mages/',''),'.gif','')
= 123
-Original Message-
From: Todd [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 07, 2004 9:23 AM
To: CF-Talk
Subject: Re: MySQL query question
A couple of reasons. 1. This wasn't thought about ahead of time and
Todd [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 07, 2004 8:23 AM
To: CF-Talk
Subject: Re: MySQL query question
A couple of reasons. 1. This wasn't thought about ahead of time and there
is several months worth of data in the table already. 2. I have no control
over the database
"Eric Creese" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 07, 2004 10:08 AM
Subject: RE: MySQL query question
> why would you not store the id in the second table as a FK to the PK in
> table one. I think that is just good tabl
PROTECTED]
Sent: Tuesday, December 07, 2004 8:47 AM
To: CF-Talk
Subject: MySQL query question
In one table I have an ID as the primary key. In a second table, I have a
string that is made out of that ID. I'm trying to match the two. The string
looks like this: /folder1/fo
In one table I have an ID as the primary key. In a second table, I have a
string that is made out of that ID. I'm trying to match the two. The string
looks like this: /folder1/folder2/some_other_stuff_that_changes/123.txt where
123 is the ID from the first table. How can I match the two us
Marco Antonio C. Santos wrote:
> How can I do MySQL works fine with this simple query:
>
> SELECT DISTINCT User_ID,(SELECT Count(User_ID) FROM users p1 WHERE
> p1.User_ID = users.User_ID) AS CountID
> FROM users
SELECT User_ID, count(User_ID)
FROM users
GROUP BY User_ID
Jochem
[Todays Threads]
How can I do MySQL works fine with this simple query:
SELECT DISTINCT User_ID,(SELECT Count(User_ID) FROM users p1 WHERE
p1.User_ID = users.User_ID) AS CountID
FROM users
MySQL don't have subselect capabilities, so
We're using 4.0.12 version in Win2K3 server...
Thanx
[Todays Threads]
[Th
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 8:16 AM
To: CF-Talk
Subject: Re: mysql query help
You know, I've crafted two responses to this already (this is the third)
and both ended up being wrong after I thought about it for a while.
Someone else may be able to post the "r
You know, I've crafted two responses to this already (this is the third)
and both ended up being wrong after I thought about it for a while.
Someone else may be able to post the "right" solution, but in the
interim, you could
SELECT user, max(lineupid) as maxlineupid FROM tablename
group by us
Good morning,
I'm having a devil of a time with a mysql (v3.23) query and was hoping
someone here could give me a hand.
I have a fantasy football project I'm working on and I allow registered
users to pick a lineup every week and then I score them at the end of the
week depending on how well thei
Thanks to all those who answered
Even after changing the the day() to dayofmonth(), the queries would still
not pull out the request information...
After a little reflection, I noticed that something had changed this DB
was originally an Access DB, and it was converted to MySQL, in the
Stephen Moretti wrote:
> And I disagree with both of you as it is a straight forward date comparison!
>
> Of course all you're doing is comparing two dates, so you can do the
> following instead:
>
> Depending on whether you are using an ODBC connector or not :
>
> WHERE theDate = #CreateODBCD
And I disagree with both of you as it is a straight forward date comparison!
Of course all you're doing is comparing two dates, so you can do the
following instead:
Depending on whether you are using an ODBC connector or not :
WHERE theDate = #CreateODBCDate(CreateDate(session.year, querymonth,
Ben Forta wrote:
> The *right* way to do this is to use DATEDIFF(), but alas that too was
> added in 4.1.1.
The SQL standard does not know DateDiff(). The SQL standard does
know Extract(). So I disagree with your right way.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
Yves,
You need to use DayofMonth() instead of Day() - Day() is available in
4.11 and is a synonym of DayofMonth()
I'm sure that mySQL will ignore the single quotes around dayVal,
querymonth and session.year, but the functions that you are comparing
against do generate numeric values, so you do
> Is this a valid query in MySQL 3.xx ?
>
>
> SELECT *
> FROM events
> WHERE day(theDate) = '#dayVal#' and month(theDate) =
> '#querymonth#' and
> year(theDate) = '#session.year#'
>
Day() can not be used in MySQL WHERE clause
Use To_Days() to convert both dates, here's an example:
To_Days(Po
The *right* way to do this is to use DATEDIFF(), but alas that too was
added in 4.1.1.
--- Ben
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 21, 2004 9:54 AM
To: CF-Talk
Subject: Re: MySQL query
Yves Arsenault wrote:
>
> Is
Yves Arsenault wrote:
>
> Is this a valid query in MySQL 3.xx ?
No. http://www.mysql.com/doc/en/Date_and_time_functions.html says
rather explicitly that Day() was added in 4.1.1
Use Extract().
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads]
Hello,
I just wanted to check...
Is this a valid query in MySQL 3.xx ?
SELECT *
FROM events
WHERE day(theDate) = '#dayVal#' and month(theDate) = '#querymonth#' and
year(theDate) = '#session.year#'
A client is using a calendar app on a site he is putting on my server, he
has always used Wind
esday, February 11, 2003 9:52 AM
Subject: RE: CFMX/mySQL Query Problem
> Yes sorry my bad, however the error message is pointing out a problem with
> datatypes. How about id field? is that a string or a value? because you're
> trying to insert it as a string although it loo
ailto:[EMAIL PROTECTED]]
Sent: 11 February 2003 15:21
To: CF-Talk
Subject: Re: CFMX/mySQL Query Problem
But that would make NULL a string instead of a NULL value ...
Paul Giesenhagen
QuillDesign
- Original Message -
From: "Dave Wilson" <[EMAIL PROTECTED]>
To: "CF-Talk
Paul Giesenhagen wrote:
>
> password="#form.dbPassword#">insert into
> settings(id,datatype,smalltext,largetext,numberdata,datedata,edit_date,admin
> _user) values('36','urlRoot',' cfsqltype="CF_SQL_VARCHAR">',NULL,NULL,NULL,NULL,'#form.adminUser#') y>
Loose the quotes around the cfqueryparam.
t;[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:35 AM
Subject: Re: CFMX/mySQL Query Problem
> Its daft and people will shout at me, but try this instead :
>
> password="#form.dbPassword#">
> insert into settin
If the columns allow NULL values, could you not just omit them?
- Original Message -
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
> What the insert looks like:
> values('36','urlRoot',' (param 1) ',NULL,NULL,NULL,NULL,'admin') are the
> values being put into the database.
~~
ie. without the cfqueryparam.
Regards
Stephen
- Original Message -
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 3:18 PM
Subject: Re: CFMX/mySQL Query Problem
> Lets try that one aga
ay, February 11, 2003 7:19 AM
To: CF-Talk
Subject: Re: CFMX/mySQL Query Problem
Lets try that one again! (copy and paste just didn't cut it)
I have a query that is giving a generic error and I cannot see what hte
problem is ... below you will see the insert statement and the generic
error.
But that would make NULL a string instead of a NULL value ...
Paul Giesenhagen
QuillDesign
- Original Message -
From: "Dave Wilson" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:13 AM
Subject: RE: CFMX/mySQL Quer
n
- Original Message -
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:11 AM
Subject: CFMX/mySQL Query Problem
> I have a query that is giving a generic error and I cannot see what hte
p
Paul,
At first glance I think you've omitted single quotes around the largetext
value i.e. the first NULL in your values list below should be 'NULL'
Dave
-Original Message-
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
Sent: 11 February 2003 15:12
To: CF-Talk
Subj
I have a query that is giving a generic error and I cannot see what hte problem is ...
below you will see the insert statement and the generic error.This is running on mySQL
3.2x and Windows XP, the #variables.urlRoot# is http://localhost/insert into
settings(id,datatype,smalltext,largetext,numb
How to change this query from MS Access to MySQL Query
Rick,
>
> Instead of this:
>
>
> Select * from Properties where PropertyID
> in (Select PropertyID from Rentals where Rentals.ClientID
> in (Select ClientID from Clients where Clients.C
Rick,
>
> Instead of this:
>
>
> Select * from Properties where PropertyID
> in (Select PropertyID from Rentals where Rentals.ClientID
> in (Select ClientID from Clients where Clients.ClientID
> = #App_ClientID#))
>
>
> I use this:
>
>
>
> I didn't get an error when I ran your query below using the left join,
> but wouldn't the "WHERE Units.UnitID = #App_ClientID#"
> need to be "WHERE Clients.ClientID = #App_ClientID#" ?
> or am I not understanding the join logic...
>
Opps... Yes Sorry...
That's what you get with changing so
Wednesday, December 11, 2002 1:47 PM
To: CF-Talk
Subject: RE: How to change this query from MS Access to MySQL Query
Thanks, Gordon...works good.
How would I change this one to make it mySQL compatible?
Select * from Properties where PropertyID
in (Select PropertyID from Rent
= #App_ClientID#))
Rick
<><
-Original Message-
From: Cary Gordon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 1:22 PM
To: CF-Talk
Subject: Re: How to change this query from MS Access to MySQL Query
SELECT u.*
FROMUnits u, Clients c
WHERE c.UnitID =
SELECT u.*
FROMUnits u, Clients c
WHERE c.UnitID = u.UnitID
AND c.ClientID = #App_ClientID#
At 11:49 AM 12/11/2002 -0500, you wrote:
>Hi, all.
>
>This query worked with MS Access, but not with MySQL...
>
>How should I change this to make it work?
>Is it a subquery problem?
>Thi
sage-
From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 1:00 PM
To: CF-Talk
Subject: Re: How to change this query from MS Access to MySQL Query
>
> This query worked with MS Access, but not with MySQL...
>
> How should I change this to make it work?
pported in MySQL currently, but will be in 4.1... :o(
I'll work on the joins...thanks...
Rick
<><
-Original Message-
From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 1:00 PM
To: CF-Talk
Subject: Re: How to change this query from MS Access t
>
> This query worked with MS Access, but not with MySQL...
>
> How should I change this to make it work?
> Is it a subquery problem?
> This is MySQL version 4.0.5 beta...
>
In mySQL you need to use Joins.
Select *
FROM Units LEFT JOIN Clients
ON Units.UnitID = Clients.UnitID
Rick Faircloth wrote:
>
> How should I change this to make it work?
> Is it a subquery problem?
> This is MySQL version 4.0.5 beta...
>
>
> Select * from Units U where U.UnitID
> in (Select UnitID from Clients where Clients.ClientID
> = #App_ClientID#)
>
Hi, all.
This query worked with MS Access, but not with MySQL...
How should I change this to make it work?
Is it a subquery problem?
This is MySQL version 4.0.5 beta...
Select * from Units U where U.UnitID
in (Select UnitID from Clients where Clients.ClientID
84 matches
Mail list logo
