DISTINCT search mySql & Coldfusion

Databases
1

I have the following query:


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

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

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

this leads to the obvious question: if the original query had more columns than you actually needed for output, why?