Htykuut

I have a table like these:

menu_id menu_name menu_description

i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?

UPDATE:
with this query i get the most relevant results on top:

    select menu_id, menu_name, menu_description from ( select menu_id, 

    menu_name, menu_description, case when menu_name like '%salame%' 

    then 1 else 0 end + case when menu_description like '%salame%' 

    then 1 else 0 end as order_value from z8upvan6w_menus) as t order 

    by order_value desc

results:

    menu_id | menu_name              | menu_description

    13      | Panino Salame e Noci   | Salame, Noci, Caprino e Insalata

    14      | Piadina Salame e Noci  | Salame, Noci, Caprino e Insalata

    11      | Panino Caprese         | Mozzarella, Pomodoro e Insalata

    12      | Panino Boscaiolo       | Speck, Brie e Salsa Boscaiola

my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?

You are not filtering out any records. For that, you need a where clause:

select menu_id, menu_name, menu_description

from from z8upvan6w_menus

where (menu_name like '%salame%') or

      (menu_description like '%salame%' )

order by ( (menu_name like '%salame%') +

           (menu_description like '%salame%')

         ) desc;

Notes:

• The where clause does the filtering.


• No subquery is necessary. You can order by an expression.


• I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:

It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.

UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.

Assuming @input_val is the value you're trying to find in the columns.

select menu_id, menu_name, menu_description

from (

 select menu_id, menu_name, menu_description, 

 case 

    when menu_name like '%' + @input_val + '%' then 1

    else 0

 end

    +

 case

    when menu_description like '%' + @input_val + '%' then 1

    else 0

 end

 as order_value

 from menu_table) as t

 where t.order_value > 0

 order by order_value desc, menu_name asc