I want to construct a MySQL query that will help me identify users who have uploaded a profile picture but not set an avatar. Picking out users who haven't chosen an avatar from the gallery is easily accomplished from a simple query, but I'm having trouble filtering out the ones who have set a custom avatar. The queries I've come up with all end up filtering out the ones who haven't set a custom avatar instead, so I get a list of the members who have set one. I've been trying to experiment with the NOT EXISTS subquery, but I haven't got the syntax right.
Code:
SELECT username, posts
FROM vB_user as user
JOIN vB_customavatar as avatar
ON user.userid = avatar.userid
WHERE user.avatarid = '0' AND (WHERE NOT EXISTS avatar.userid)
ORDER BY posts desc;
Can anyone point out what I'm doing wrong? (I haven't even gotten to the part where I add in the ones who have set profile pictures!)