Hi, There is something I cannot figure a simple way of doing it, either in SQL or in CF.
JOINS in SQl are relly handful, but sometimes, they can generate qute big queries for nothing. Suppose I have this: SELECT <table1.many columns>, table2.justOneColumn FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2 Suppose that Table2 contains a rather large number of records for each record in table1. The result will be a pretty large data set, with most information repeated in all columns except for justOneColumn, quite a useless overhead. What would be much more efficient in some instances would be to have only one row for every record in table1, all rows in the join table represented by a list. Something like: SELECT <table1.many columns>, LISTOF(table2.justOneColumn) FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2 I do not see anything like this in SQL; Of course, it could be done in CF using a GROUP in a CFOUTPUT to create a new Query, but the overhead would be worse. Any Idea ? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335118 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
