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
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 r...@whitestonemedia.comwrote:
Hi, guys...
I've been writing, research, and re-writing a query
over and over trying to get the correct (ie, trustworthy)
results, but
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
- 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
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 query
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!
and 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 TimesUsed
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!
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
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
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 column
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 mySQL
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)
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 created -
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
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
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 regards,
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
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 saw
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
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
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
name
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
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
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' is
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(' ',
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:
[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?
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 INNER JOIN
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 -
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
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 = cfqueryparam value=#cUserID#
cfsqltype=cf_sql_integer
GROUP BY o.UserID
I
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 cfif like this ?
cfif qGetMatches.rowcont NEQ 0
tr ..
/tr
/cfif
Not ideal I know
Simon - Here's the dumps - the GetMatches dump has never
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
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
Cheers
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 = cfqueryparam value=#cUserID#
cfsqltype=cf_sql_integer
GROUP BY o.UserID
Jochem
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 trying
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
UserID: 13
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 with
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
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/folder2
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 table design and will easily
accomplish what you
]
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. This makes for a bad combination :) So
/',''),'.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 there
is several months worth of data
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 can I
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 future...)
But, since I've worked in similar situations with databases
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, 2004 10:37 AM
Subject: Re: MySQL query question
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]
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
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
CFQUERY NAME=getrelevantrows
SELECT user, max(lineupid) as maxlineupid
, 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 right solution, but in the
interim, you could
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
Hello,
I just wanted to check...
Is this a valid query in MySQL 3.xx ?
cfquery name=There datasource=ipe
SELECT *
FROM events
WHERE day(theDate) = '#dayVal#' and month(theDate) = '#querymonth#' and
year(theDate) = '#session.year#'
/cfquery
A client is using a calendar app on a site he 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]
[This
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 this a valid
Is this a valid query in MySQL 3.xx ?
cfquery name=There datasource=ipe
SELECT *
FROM events
WHERE day(theDate) = '#dayVal#' and month(theDate) =
'#querymonth#' and
year(theDate) = '#session.year#'
/cfquery
Day() can not be used in MySQL WHERE clause
Use To_Days() to convert both
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
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
-
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,
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 =
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/cfquery
datasource=#form.dsn# name=step1
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
Subject: CFMX/mySQL
type requested
Any help would be greatly appreciated!
Paul Giesenhagen
QuillDesign
- 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
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 Query Problem
Paul,
At first glance I
, 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.
This is running
', '#variables.urlRoot#', NULL, NULL, NULL, NULL,
'#form.adminUser#')
/cfquery
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
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.
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 :
cfquery datasource=#form.dsn# name=step1 username=#form.dbUserName#
password=#form.dbPassword#
insert
Paul Giesenhagen wrote:
cfquery datasource=#form.dsn# name=step1 username=#form.dbUserName#
password=#form.dbPassword#insert into
settings(id,datatype,smalltext,largetext,numberdata,datedata,edit_date,admin
_user) values('36','urlRoot','cfqueryparam value=#variables.urlRoot#
:[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 [EMAIL PROTECTED]
Sent
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 looks like it should be a
value
to me.
Dave
-Original
Rick,
Instead of this:
CFQUERY Name=GetProperty Datasource=#DSN#
Select * from Properties where PropertyID
in (Select PropertyID from Rentals where Rentals.ClientID
in (Select ClientID from Clients where Clients.ClientID
=
MS Access to MySQL Query
Rick,
Instead of this:
CFQUERY Name=GetProperty Datasource=#DSN#
Select * from Properties where PropertyID
in (Select PropertyID from Rentals where Rentals.ClientID
in (Select ClientID from Clients where Clients.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...
CFQUERY Name=GetUnit Datasource=#DSN#
Select * from Units U where U.UnitID
in (Select UnitID from Clients
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...
CFQUERY Name=GetUnit Datasource=#DSN#
Select * from Units U where U.UnitID
in (Select UnitID from Clients where Clients.ClientID
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.
CFQUERY Name=GetUnit Datasource=#DSN#
Select *
FROM Units LEFT JOIN Clients
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 to MySQL Query
This query
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?
Is it a subquery problem?
This is MySQL version 4.0.5 beta
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?
This is
Clients where Clients.ClientID
= #App_ClientID#))
/CFQUERY
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.*
FROM
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: 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?
CFQUERY Name
83 matches
Mail list logo
