CODEWITHSUNDEEP
mysqlsearch
Mike Casan Ballester
Mike Casan Ballester

Reputation: 1770

Using WHERE clause in mySQL and searching for a a string in two columns

I have a query that searches a 'string' in two fields, FIELD_IMPORTANT and FIELD_2.

I want to have the result;

IF the 'string' is found in FIELD_IMPORTANT THEN order the results with the records having the 'string' in FIELD_IMPORTANT at the TOP, and then the others...

Is it possible ?

Up to now I have the following:

SELECT
expert.expert_name, naf_add.FIELD_IMPORTANT, naf_level_5.FIELD_2,
ROUND(AVG(expert_rating.rating_global)) AS avg_rating_global

FROM naf_nomenclature

LEFT JOIN naf_add ON naf_add.naf_add_naf_level_5_code = naf_nomenclature.level_5_code
INNER JOIN naf_level_5 ON naf_level_5.level_5_code = naf_nomenclature.level_5_code
INNER JOIN expert ON expert.expert_level_5_code = naf_nomenclature.level_5_code
LEFT JOIN expert_rating ON expert_rating.rating_expert_id = expert.expert_id


WHERE 
naf_add.FIELD_IMPORTANT  LIKE '%elec%' OR
naf_level_5.FIELD_2 LIKE '%elec%'

GROUP BY expert.expert_name

ORDER BY fword DESC, avg_rating_global DESC

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can use expressions in the order by:

order by (naf_add.FIELD_IMPORTANT LIKE '%elec%') desc,
         fword, avg_rating_global

MySQL treats boolean expressions as integers in a numeric context, with "0" for false and "1" for true. So, when the expression is true, then the value of the first key is "1". The desc puts these first.

Upvotes: 1

Related Questions