Hi, I have a large query which uses many left joins in order to list a directory of contacts.
Select id, name, summary, countryname, categoryID, categoryName >From table_contacts left join ..... Where ... The where statement includes cfif statements in order for the user to choose which category (or not) to list contacts by. The directory has associations with several different categories and subcategories. My Q works fine, but I only want it to return ONE row for each entry. Some entries have several categories associated with them - but I don't care, I just want one row, and one category, any one. In order to do this I've used the MIN() function, which will in effect return each contact with the first category they were assigned to: Select id, name, summary, countryname, MIN(categoryJunctionID) as ted >From table_contacts left join ..... Where ... Group by ... This works fine, but then I have to cfloop over the Q results, doing another Query to get the values of the cateogryIDs and Names where categoryJunctionID = #ted# This works, but I think it would be slow, esp if the table gets quite big - surely this seems like something that can be done in one go. I just don't know how to do it. What I WANT to do is something like: Select id, name, summary, countryname, MIN(categoryJunctionID) as ted, (select categoryID, categoryName, subcatID, subcatName from ..... where categoryJunctionID = ted) as >From table_contacts left join ..... Where ... Group by ... But this throws an error. One being using too many columns in a subquery, the other the aggregate function. There's an error even if I select just one column and change "= ted" to "= MIN(...)" We are on Coldfusion 5.0 and I'm using MS Access database. Any suggestions would be very much appreciated. Thanks! S ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
