CODEWITHSUNDEEP
sqlcoldfusiongroup-by
Phillip Perry
Phillip Perry

Reputation: 27

Group attribute in Coldfusion not working right

I'm trying to display options for homes and the categories in which they're in. Formatted in this way:

Category 1 Category 2
---------- ----------
option 1   Option 2

I have gotten very close. The options are showing up as expected, but the categories are repeating themselves. I've included an image.

Repeating categories

I've tried all combinations of group in the CFOUTPUT and in the SQL with no success. I would appreciate some ideas. This is my current code:

<cfquery datasource="applewood" name="categorize">
    SELECT idOptions
           , options.option
           , options.idOpCategories
           , op_categories.categoryName
    FROM  options, op_categories
    GROUP BY idOptions
</cfquery>

 ...
<cfoutput query="categorize" group="idOpCategories">
    <h3>#UCASE(categoryName)#</h3>
    <ul>
        <cfoutput>
            <li>#option#</li>
        </cfoutput>
    </ul>
</cfoutput>

Update:

I ordered and grouped by the same column and getting this result:

Ordered and groupd by same column

Upvotes: 0

Views: 351

Answers (1)

Leigh
Leigh

Reputation: 28873

You are missing a JOIN clause between the "options" and "op_categories" tables, so the result is an implicit CROSS JOIN or cartesian product:

... it will produce rows which combine each row from the first table with each row from the second table

Hence the duplicates. You need to join the two tables on the related column ie the category id.

Also, as Scott mentioned in the comments, cfoutput group only works with sorted query results. However, the results must be sorted by the same columns - in the same order - as your grouping. Otherwise, it can create the appearance of duplicated categories. So if you are grouping by "CategoryName" first, you should order by "CategoryName" first. 

   SELECT cat.categoryName, o.option, ...
   FROM   options o
             INNER JOIN op_categories cat
                ON cat.idOpCategories = o.idOpCategories
   ORDER BY cat.categoryName, o.option

   <cfoutput query="..." group="categoryName">
       ... more code ...
   </cfoutput>

(In this specific case, you could technically use the category id as well)

Upvotes: 4

Related Questions