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 <[email protected]>: > > 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:321585 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
