SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,963
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DISTINCT search mySql & Coldfusion

    I have the following query:
    Code MySQL:
    SELECT C.company_id, C.county_eng, C.city_eng, C.category_eng, C.activity_eng, C.company_name, C.company_description_eng, CO.county_eng, CI.city_eng, CA.category_eng, A.activity_eng
        <cfif structKeyExists( Url, 'txtSearch' ) AND Url.txtSearch NEQ ''>
        ,MATCH( C.county_eng, C.county_gr, C.city_eng, C.city_gr, C.category_eng, C.category_gr, C.activity_eng, C.activity_gr, C.company_name, C.company_description_eng, C.company_description_gr ) AGAINST ('#txtSearch#*') as Relevance
    	</cfif> 
        FROM companies C
        INNER JOIN counties CO ON C.county_id = CO.county_id
        INNER JOIN cities CI ON C.city_id = CI.city_id
        INNER JOIN categories CA ON C.category_id = CA.category_id 
        INNER JOIN company_activities COA ON C.company_id = COA.company_id
        INNER JOIN activities A ON COA.activity_id = A.activity_id
        WHERE 0 = 0
        <cfif structKeyExists( Url, 'category_id' ) AND Url.category_id NEQ 0>
        AND C.category_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.category_id )#" /> )
        </cfif>
    	<cfif structKeyExists( Url, 'activity_id' ) AND Url.activity_id NEQ 0>
        AND COA.activity_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.activity_id )#" /> )
        </cfif>
        <cfif structKeyExists( Url, 'txtSearch' ) AND Url.txtSearch NEQ ''>
        AND MATCH (C.county_eng, C.county_gr, C.city_eng, C.city_gr, C.category_eng, C.category_gr, C.activity_eng, C.activity_gr, C.company_name, C.company_description_eng, C.company_description_gr ) AGAINST ( '#txtSearch#*' IN BOOLEAN MODE )
        </cfif>
    Search is done by either a text field(txtSearch) or two drop down menus (category_id, activity_id) When someone just uses the category_id dropdown, or the txt field I get certain companies multiple times! This is because a company can be categorized in more than one activity in a certain category. I tried DISTINCT, but that doesn't change a thing. What should I change to get the companies just once?

    Thank you in advance

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Get the activity out of the query is one way.
    If you need it, then you'll have to decide which one you want to extract. Or extract them all, like you do now, and then loop through the result and handle the duplicate companies with your programming language.

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,963
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Get the activity out of the query is one way.
    If you need it, then you'll have to decide which one you want to extract. Or extract them all, like you do now, and then loop through the result and handle the duplicate companies with your programming language.
    Thank you for the reply Guido. I just found the solution, indeed in the programming language(Coldfusion) I made a Q o Q with only the fields needed for the output and used the Distinct in that one and it's working great.

    Thank you again

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by donboe View Post
    I made a Q o Q with only the fields needed for the output
    this leads to the obvious question: if the original query had more columns than you actually needed for output, why?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •