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,213422222 > 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
