Hi
we have a relational database and one task is taking far too long but we cannot
see any way to improve it, although i do feel there is a way to massively
improve it... so would like some expert help on this
we have a normal table which has a One-to-Many table coming off of it...
table 1
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId
group by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
The easiest way is use a JOIN to get a query that will have a phone number
on each row, then use the 'group' attribute of cfoutput to display them
correctly.
On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote:
Hi
we have a relational database and one task is taking far
Have you looked into StoredProcs? Push the load off the web server onto the
SQL Server (or Oracle whichever)
On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote:
Hi
we have a relational database and one task is taking far too long but we
cannot see any way to improve
There isn't really an efficient way to get a comma separated list in
one column with your DB query. However, there is a far more efficient
method that uses a single query and groups the output:
cfquery name=qryClients datasource=myDsn
SELECT c.clientId, c.firstname, c.lastname, t.number
FROM
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat. it
works like a dream and where this task was taking 23 seconds to complete it is
now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes
: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter
valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies
14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds
...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks
group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using
13 matches
Mail list logo
