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