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 ?
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.
Please, provide else part inside of case statement query :
Format : case
when condition_here then
true_statemnt
else
false_statemnt
end
===============================
CASE WHEN m.meta_key = 'c_user_level' THEN m.meta_value else 0 END