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
