StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Case not evaluating correctly. Where did I went wrong?
text
Body
copied!<p>This is the query I am executing to search for the autocomplete function for keyword "sector 2"</p> <pre><code>SELECT display_text,token, relevancy , (CASE token WHEN token = 'sector' THEN relevancy*1000 WHEN token = '2' THEN relevancy*1000 ELSE relevancy / 100 END) as real_relevancy FROM location_search WHERE display_text LIKE '%sector%' AND display_text LIKE '%2%' ORDER BY real_relevancy DESC </code></pre> <p>The table have five fields: id , area_id, display_text, token and relevancy. display text is what i want to show in auto complete search result. And token contains the display text split by spaces.</p> <p>like for a display text "Sector 2 Noida" there will be 3 entries in the table</p> <pre><code>id | area_id | display_text | token | relevancy 6 | 234 | Sector 2 Noida | sector | 441 7 | 234 | Sector 2 Noida | 2 | 986 8 | 234 | sector 2 Noida | noida | 660 </code></pre> <p>And similarly for all other display areas. Its pretty much clear by the query what I am trying to do here, that is getting more relevant results on the top.</p> <p>The problem is that the first case evaluates true and is execulted in random cases where token is niether sector nor 2. </p> <p>Like the first reult in resultset is</p> <pre><code>display_text | token | relevancy | real_relevancy Sector 29 - Powergrid , Gurgaon | Powergrid | 789 | 789000 </code></pre> <p>Why is it happening. WHere did I go wrong with the query? Probably m doing some silly mistake. Please help.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload