MySQL CASE WHEN where clause causes failure

Question

I have a complex query that does multiple matches across multiple columns and then orders by relevance.

Everything works fine UNTIL I add WHERE 'rank' > 0

This then returns an empty results set.

If I remove the 'WHERE' statement then I can see all results with the highest matches at the top.

Could someone help me work out 'WHERE' :-D I am going wrong!!

SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
AS rank 
FROM clients 
WHERE rank > 0
ORDER BY rank

EDIT

I removed the single quotes around the rank word and now get 'unknown column rank in where clause'

Remove the single quotes from `Rank` in both the places and try — MusicLovingIndianGirl, Dec 24 '15 at 10:07
Unknown Column rank in WHERE Clause if I do that so at least that is something - I was masking the problem - but how do i fix that? :-P — GrahamTheDev, Dec 24 '15 at 10:09
I don't think MySQL would support `WHERE` clause with `aliases` - check http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql — MusicLovingIndianGirl, Dec 24 '15 at 10:11
Yup you were right - so easy when you know how - changed to HAVING rank > 0 and works fine. Add as an answer and I will mark as correct - thanks. — GrahamTheDev, Dec 24 '15 at 10:14

score 1 · Accepted Answer · edited May 23 '17 at 12:04

Remove single quotes from rank and try. Anyway, I don't think MySQL would support WHERE clause with aliases - check this.

Use HAVING rather than WHERE:

SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END + CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END + CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END + CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END AS rank FROM clients HAVING rank > 0 ORDER BY rank

score 0 · Answer 2 · answered Dec 24 '15 at 10:17

Try this:

SELECT * 
FROM (SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
              AS rank 
        FROM clients 
      ) AS A 
WHERE rank > 0
ORDER BY rank;

no doesnt work - musiclovingindiangirl got it right it was that `WHERE` doesnt work with an alias. Need to use `HAVING` instead. Thanks though. — GrahamTheDev, Dec 24 '15 at 10:22

MySQL CASE WHEN where clause causes failure

2 Answers2