expert sql challenge
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 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321581 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
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 STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb On Tue, Apr 14, 2009 at 12: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 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 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321582 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
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 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 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
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 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 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321584 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
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 client c LEFT JOIN clientPhoneNumber t ON t.clientId = c.clientId /cfquery ... cfoutput query=qryClients group=clientId #firstname# #lastname# numbers: cfoutput#number#/cfoutput /cfoutput ... Its a shame you can't do group on the cfloop tag but its a wonderful thing that saves you in these kinds of cases. Dominic 2009/4/14 Richard White rich...@j7is.co.uk: 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 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321585 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 :) fantastic and thanks once again for all your replies :) 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 STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321586 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: expert sql challenge
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 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 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 STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321587 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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 STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
Here is an Oracle UDF (for lack of a better description) that I just found. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; return l_result; end join; Syntax: join(cursor(select name form users)). If you eant to change the default delim (which is a comma) you would do this: join(cursor(select name form users), '|') On Tue, Apr 14, 2009 at 3:31 PM, C. Hatton Humphrey chumph...@gmail.comwrote: Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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 STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321593
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321596
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
I don't know about the data function. I try to avoid SQL Server when
I can. ;) We use it for some projects with MySQL's query language
wasn't rich enough to express certain types of queries, but in general
I use MySQL when possible.
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
cheers,
barneyb
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321599
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
On Tue, Apr 14, 2009 at 3:47 PM, Barney Boisvert bboisv...@gmail.com wrote:
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
MSDN actually used almost exactly what you wrote. If you go to the MSDN page:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
And look down for C. Generating a value list using PATH mode it
shows their example.
The only real difference is that they were using AS data() which I
am not familiar with. It might be because the result returned from the
subquery in their example is being put into a tsql variable and then
used in another xml path query.
Here is the subquery in their example:
(SELECT ProductID as data()
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) as @ProductIDs
Cheers
Judah
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@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 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 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
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321600
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
