Q:

Using AND between CASE statements in the WHERE clause SQL query

I have written a query which is as follows -
SELECT user_id, display_name, user_email, meta_key, meta_value 
FROM wp_usermeta um INNER JOIN wp_users u ON um.user_id = u.id 
WHERE 
CASE 
WHEN meta_key = "renewal_date" THEN meta_value = "a"
WHEN meta_key = "wp_user_level" THEN meta_value <> "10"
END

I need the user_id of the two 'when' statements to be equal. By that I mean, if the same user(having the same user_id) has meta_key as renewal_date and corresponding meta_value as a, and the same user has meta_key as wp_user _level and has the corresponding meta_value as 10 then he should not show in the result list. With my limited knowledge of SQL I was considering using AND between the WHEN statements but it gives me a syntactic error.  

A:

From your description I think I see what you are trying to do, but your query won't work like that.  It looks like you have several rows per user id in the wp_usermeta table.  This is known as an EAV model (entity-attribute-value) and that model makes reporting quite difficult, because you have rows that should be columns.
One way of getting it working is to join the wp_usermeta table multiple times (so rows become columns essentially), like this:
SELECT u.id, u.display_name, u.user_email, 
  rd.meta_value AS renewal_date, ul.meta_value AS wp_user_level
FROM wp_users u
LEFT JOIN wp_usermeta rd ON rd.user_id=u.id AND rd.meta_key='renewal_date'
LEFT JOIN wp_usermeta ul ON ul.user_id=u.id AND ul.meta_key='wp_user_level'
WHERE rd.meta_value = 'a'
AND ul.meta_value <> '10'

Please note that users who have no 'wp_user_level' data will be included in this report.  If you need to filter those ones out you can replace LEFT JOIN with INNER JOIN.

