在php mysql中使用“CASE WHEN ... THEN”时，错误查询值为null？

Question

I have 2 table

c_users(ID, name)
        1 | peter
        2 | micheal

c_user_meta(user_id, meta_key, meta_value)
               1   |  c_user_level |  0    
               1   |  last_login   |  2013-07-31 14:33:24
               2   |  c_user_level |  1    
               2   |  last_login   |  2013-07-30 14:33:24   

And query get user last login with c_user_level = 0

$sql = " SELECT u.* , 
          max(CASE WHEN m.meta_key = 'c_user_level' THEN m.meta_value END ) level, 
          max(CASE WHEN m.meta_key = 'last_login' THEN m.meta_value END ) last_login
         FROM c_users u
         LEFT JOIN c_usermeta AS m ON m.user_id = u.ID
         WHERE u.id >0
          AND (CASE WHEN m.meta_key = 'c_user_level' THEN m.meta_value END) = 0
          AND (CASE WHEN m.meta_key = 'last_login' THEN m.meta_value END) IS NOT NULL
         GROUP BY u.id ORDER BY last_login DESC"

And result:

ID  | name | level | last_login
1   | peter| NULL  | 2013-07-31 14:33:24

Error why level of user_id = 1 is NULL, how to fix it ?

Answer 1

You probably need to skip your where condition:

SELECT
  u.* , 
  max(CASE WHEN m.meta_key = 'c_user_level' THEN m.meta_value END ) level, 
  max(CASE WHEN m.meta_key = 'last_login' THEN m.meta_value END ) last_login
FROM
  c_users u
  LEFT JOIN c_usermeta AS m ON m.user_id = u.ID
WHERE u.id >0
GROUP BY u.id
ORDER BY last_login DESC

or (depending on what you are after) use HAVING clause, to filter the aggregated value:

SELECT
  u.* , 
  max(CASE WHEN m.meta_key = 'c_user_level' THEN m.meta_value END ) level, 
  max(CASE WHEN m.meta_key = 'last_login' THEN m.meta_value END ) last_login
FROM
  c_users u
  LEFT JOIN c_usermeta AS m ON m.user_id = u.ID
WHERE u.id >0
GROUP BY u.id
HAVING level=0 AND last_login IS NOT NULL
ORDER BY last_login DESC

Please see fiddle here.