Title: CASE Statement in where clause in mysql
Tags: mysql;case;where
Question: I'm trying to fetch data from table where I'm using a CASE condition in the WHERE clause and currently I'm using following query:-

```$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", 
implode(", ", $aColumns))." FROM (select CASE when r.agent_id=24
THEN r.Unit ELSE '--' END AS MyUnit, 
CASE when r.agent_id=24 THEN r.landlord_name ELSE '--' END AS landlord_name_new, 
r.*,l.loc_name as location,sl.sub_sub_loc as sub_location,
c.category as   category,CONCAT(u.first_name, ' ', u.last_name) As agent
from crm_sales r 
LEFT JOIN crm_location l ON r.area_location_id=l.loc_id
LEFT JOIN crm_subloc sl ON sl.sub_loc_id = r.sub_area_location_id
LEFT JOIN crm_category c on c.id = r.category_id
LEFT JOIN crm_users u on u.id=r.agent_id
where r.is_active=1 AND r.is_archive=0

AND CASE agent_id WHEN r.agent_id!=24 then r.status=2 else 1=1

 group by r.ref) sel
$sWhere
$sOrder
$sLimit
";
```

Now I want to add one more condition, something like this.

IF(r.agent_id != 24) THEN WHERE r.status=2

EDITED: ADD CASE which i want but error


Here is the accepted answer: Fix the case/when clause in your WHERE clause to...

```AND CASE WHEN r.agent_id != 24 
         then r.status = 2 
         else 1 = 1 end
```



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/12/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><case><where>', 'Title': 'CASE Statement in where clause in mysql', 'ViewCount': 4857, 'id': 26886541, 'user_AboutMe': None, 'user_AccountId': None, 'user_DisplayName': None, 'user_Reputation': None, 'user_Views': None, 'user_id': None}}