instead, you can order by ID
<cfoutput query='[queryname]' group='ID'>
Use the group part as the display the person;
<cfoutput>Use the inner part to display the categoryname and
categoryID</cfoutput>
</cfoutput>
It will return many more rows than you had originally intended, but
it will reduce the trips to the database.
Hope this helps,
William
On Mar 9, 2008, at 12:51 AM, Scott OConnell wrote:
> 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:300807
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
