Eliminating duplicate results

Databases
1

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)


SELECT resources.resource_id
	, resources.resource_type
	, resources.resource_roles
	, resources.resource_level
	, resources.resource_title
	, resources.resource_url
	, resources.resource_url_exit
	, resources.resource_desc
	, resources.resource_provider
	, resources.provider_url
	, resources.provider_url_exit
	, resources.resource_type
	, resc_roles.rr_resource_id
	, resc_roles.rr_role_id
	, roles.role_id
	, roles.role_name
	, levels.level_id
	, levels.level_name 
	, resc_areas.ra_resc_id
	, resc_areas.ra_area_id
	, areas.area_id
	, areas.area_name
	, types.type_id
	, types.type_name
FROM ( ( ( ( ( (
	resources
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.

2

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!

3

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.