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
