MySQL - WHERE condition ignored if CASE is used in SELECT

Question

In my database there are users that have permanent or limited access.

status: 1 = active
status: 2 = blocked

If access is limited, there's a date from when till when it is valid. The problem is that the WHERE-condition ignores the CASE.

In this example I want only users that have status 2 (=blocked).

SELECT   `id`,
         `username`,
         CASE
           WHEN (`status` = 1 AND `valid` = 'limited' AND (`valid_from` > CURDATE() OR `valid_till` < CURDATE() ) ) THEN 2
           WHEN (`status` = 2 AND `valid` = 'limited' AND (`valid_from` > CURDATE() OR `valid_till` < CURDATE() ) ) THEN 1
           ELSE `status`
         END as `status`,
FROM     `table`
WHERE    `status` = 2
ORDER BY `id` asc

Why does the WHERE condition ignore my CASE?

Edit:

This is what my database looks like.

username | status | valid     | valid_from | valid_till
---------------------------------------------------------
Peter    | 1      | unlimited | 2017-05-01 | 2020-05-01 |
Mike     | 2      | unlimited | 2017-05-01 | 2017-05-31 |
Janet    | 1      | limited   | 2017-01-01 | 2017-02-01 |
Ari      | 2      | limited   | 2017-01-01 | 2017-02-01 |
Tina     | 1      | limited   | 2018-05-01 | 2020-05-01 |
Rick     | 2      | limited   | 2018-05-01 | 2020-05-01 |
Donald   | 1      | limited   | 2017-05-01 | 2020-05-01 |
James    | 2      | limited   | 2017-05-01 | 2020-05-01 |

Is it possible to get a right match with a CASE or do I also need to edit my WHERE condition like this:

SELECT   `id`,
         `username`,
         CASE
           WHEN (`status` = 1 AND `valid` = 'limited' AND (`valid_from` > CURDATE() OR `valid_till` < CURDATE() ) ) THEN 2
           WHEN (`status` = 2 AND `valid` = 'limited' AND (`valid_from` > CURDATE() OR `valid_till` < CURDATE() ) ) THEN 1
           ELSE `status`
         END as `status`,
FROM     `table`
WHERE  ( `status` = 2 AND `valid` = 'unlimited' ) OR
       ( `status` = 1 AND `valid` = 'limited' AND ( `valid_from` > CURDATE() OR `valid_till` < CURDATE() ) )
ORDER BY `id` asc

what type of data are your valid_from and valid_till fields? — , May 16 '17 at 08:18
@GiacomoPittalis: valid_fgrom and valid_till are date fields. — Deen, May 16 '17 at 08:22
Sql statements are not executed top down. Have a look at http://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order — P.Salmon, May 16 '17 at 08:22
_“In this example I want only users that have status 2 (=blocked)”_ – then why are you checking for `WHEN (`status` = 1` …? That makes no sense of course. status will never be 1, because you exclude all records with a status other than 2 in your WHERE clause. — CBroe, May 16 '17 at 08:23
@CBroe: A User can have status 1 (=active) but only limited access for a future date (next month for example). If this is true the returned status should be 2, because the user is currently blocked. — Deen, May 16 '17 at 08:26
You only select records with status 2. For those that are limited and outside the date range you display the status as 1 though, for the others as 2. So how is this result different from what you want? — Thorsten Kettner, May 16 '17 at 08:28
_“A User can have status 1 (=active)”_ - good for them ... but none of the records you are working on has status=1, because you specifically _excluded_ those records in your WHERE clause. You are saying, “would all men please leave the room” (your WHERE clause), and then you are saying to the remaining women in the room, “please raise your hand if you are a man” (CASE WHEN). You should not wonder that no hands get raised. — CBroe, May 16 '17 at 08:32
Your new WHERE clause includes limited status 1 records, but shouldn't it also exclude limited status 2 records? — Thorsten Kettner, May 16 '17 at 08:44
WHEN (`status` = 2 AND `valid` = 'limited' will never be satisfied. — P.Salmon, May 16 '17 at 08:50
By the way: can there only be one status record per user? Or can a user have two different limited date ranges for instance? — Thorsten Kettner, May 16 '17 at 08:58

score 0 · Answer 1 · answered May 16 '17 at 08:40

You have a column status in your table. So WHERE status = 2 dismisses all records with status 1. It seems however you want to correct the status when valid = 'limited' and the date range does not apply for today. So you want to apply the WHERE clause to this corrected status. You can do this by first getting the corrected status and then write a query on this one:

SELECT *
FROM
(
  SELECT 
    id,
    username,
    CASE
      WHEN (status = 1 AND valid = 'limited' AND (valid_from > CURDATE() OR valid_till < CURDATE() ) ) THEN 2
      WHEN (status = 2 AND valid = 'limited' AND (valid_from > CURDATE() OR valid_till < CURDATE() ) ) THEN 1
      ELSE status
    END as status_today
  FROM table
) corrected
WHERE status_today = 2
ORDER BY id asc;

MySQL - WHERE condition ignored if CASE is used in SELECT

1 Answers1