SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Eliminating duplicate results

    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)

    Code SQL:
    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.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!
    <cfset myblog = "http://cydewaze.org/">

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by subhbwn View Post
    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.
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •