Database Menu

[pwizzard]

I currently have a page that has a menu section that is populated from a
database table called category, the categories are for example TVs, DVDs etc,
the user can select a category and all the products within that category are
displayed, the client now wishes to change the menu to include the
manufacturers for each particular category. The manufacturers are in another
table called manufacturer which is linked to another table called products, I
have managed to join the tables together but my query returns the manufacturer
for each product, so I may for example get Sony returned several times, whereas
I really need the manufacturer that have products within that category to only
appear once.

I hope that makes sense.

<cfquery name="rsProductGroups" datasource="audiovisual">
SELECT Category.ProductGroup, Category.SidebarGraphic, Category.Ordered,
Category.CategoryID, Category.Show, Manufacturer.Manufacturer
FROM Manufacturer INNER JOIN (Category INNER JOIN Products ON
Category.CategoryID = Products.CategoryID) ON Manufacturer.ManufacturerID =
Products.ManufacturerID
WHERE Category.show = true
ORDER BY Category.Ordered desc, Manufacturer.manufacturer</cfquery>

[rmorgan]

<cfquery name="rsProductGroups" datasource="audiovisual">
SELECT Category.ProductGroup, Category.SidebarGraphic, Category.Ordered,
Category.CategoryID, Category.Show, Manufacturer.Manufacturer
FROM Manufacturer INNER JOIN (Category INNER JOIN Products ON
Category.CategoryID = Products.CategoryID) ON Manufacturer.ManufacturerID =
Products.ManufacturerID
WHERE Category.show = true
ORDER BY Category.Ordered desc, Manufacturer.manufacturer
GROUP BY Manufacturer.manufacturer
</cfquery>

[pwizzard]

Tried that and get the following error:

Message from server: Error Executing Database Query. Syntax error (missing
operator) in query expression 'Manufacturer.manufacturer GROUP BY
Manufacturer.manufacturer'.
The error occurred on line 1.

[rmorgan]

My mistake, group by comes before order by.

[pwizzard]

I also tried that and got this error:

Message from server: Error Executing Database Query. You tried to execute a
query that does not include the specified expression 'ProductGroup' as part of
an aggregate function.
The error occurred on line 1.

[rmorgan]

Check your where statement in some flavors of sql, 'show' is a reserved word. May need to rename the column.

[pwizzard]

If I actually add all the tables to the GROUP BY Category.ProductGroup,
Category.SidebarGraphic, Category.Ordered, Category.CategoryID, Category.Show,
Manufacturer.manufacturer
it now works??

Does this make sense

Thanks for your help, very much appreciated

[Dan Bracuk]

It makes sense because that's how group by clauses work. It probably doesn't
solve your problem though. Grouping without an aggregate in the select clause
rarely accomplishes anything.

cfoutput has a group attribute that would probably be useful in your
situation. Details are in the cfml reference manual. If you don't have one,
the internet does.