How to use the where in where argument

Question

I want to get only status = 1 record. But I do not have a status column in my table. So I got the value using CASE ... WHEN ... THEN . But when I try to use the where argument in where, it shows a syntax error.

my request

SELECT SQL_CALC_FOUND_ROWS *, CASE WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1' ELSE '0' END AS STATUS FROM table_coupon_code WHERE (CASE WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1' ELSE '0' END AS STATUS) = '1' AND coupon_status <> '2' 

How can i do this?

Answer 1

remove AS STATUS from where clause

 SELECT SQL_CALC_FOUND_ROWS * , CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date) THEN '1' ELSE '0' END AS STATUS FROM table_coupon_code WHERE CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date) THEN '1' ELSE '0' END = '1' AND coupon_status <> '2' 

But your CASE really not needed. Just use the CASE condition as a standalone WHERE condition, for example

 [...] WHERE quantity > num_used AND CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date AND coupon_status <> '2' 

Answer 2

If you do not want to repeat the case statement, you can wrap the selection in a subquery or make a presentation. Subselect is something like

 select status from (select case when zip like '4321%' then 1 else 0 end as status from adr ) t where status = 1; 

Answer 3

No one suggested a HAVING offer?

This allows you to query selected columns instead of actual results. Great for use on occasion and feature calls.

Answer 4

I assume that you have another, more complex query, since the one you provided is equivalent to:

 SELECT SQL_CALC_FOUND_ROWS * , '1' AS STATUS FROM table_coupon_code WHERE quantity > num_used AND CURDATE() BETWEEN coupon_start_date AND coupon_end_date AND coupon_status <> '2'