I'm building a web tool for my office that is basically a form consisting of numerous checkboxes and radio buttons where users can choose different categories and then view resources related to those categories.
Users basically chose their role, then a category (resource type), then a sub category (resource area), and finally their skill level (resource level), before submitting. After submitting they get a list of resources. Each resource can match only one category, but it can match more than one subcategory. My query looks like this:
(I've removed the CF code from the query for readability)
FROM ( ( ( ( ( (
INNER JOIN resc_roles
ON resc_roles.rr_resource_id = resources.resource_id
INNER JOIN roles
ON roles.role_id = resc_roles.rr_role_id
INNER JOIN types
ON types.type_id = resources.resource_type
INNER JOIN levels
ON levels.level_id = resources.resource_level
INNER JOIN resc_areas
ON resc_areas.ra_resc_id = resources.resource_id
INNER JOIN areas
ON areas.area_id = resc_areas.ra_area_id
WHERE resc_roles.rr_role_id = #role#
AND types.type_id IN (#type#)
AND resc_areas.ra_area_id IN (#savearea#)
AND resources.resource_level <= #levels#
ORDER BY resources.resource_level, types.type_id
The CF code groups the results by resource level (skill level) first, then by resource type next.
This works well, except if a user chooses multiple subcategories, in which case you can get the same resource appearing more than once. I'd like to get it to omit duplicates, and I'd rather do it in the SQL than fiddle it in the code.
AHA! It came to me after a piece of chocolate (typical for me). I added the resource_id to the ORDER BY clause, then modified my CF code a bit to match. Fixed!
Whenever I fool with GROUP BY I seem to trigger the dreaded "You tried to execute a query that does not include the specified expression 'foo' as part of an aggregate function." error.