MySQL for finding members with profile pics but not avatars

[Soliloquy]

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.

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!)

[kh99]

It looks like if a user has no avatar, the user.avatarid is 0 and there's no record in the customavatar table with that userid. So I think you want.

SELECT username, posts
FROM vb_user as user
WHERE user.avatarid = '0' AND 
    NOT EXISTS (SELECT * FROM vb_customavatar AS custom WHERE custom.userid = user.userid)
ORDER BY posts desc

[Soliloquy]

Thanks kh99, from that I was able to figure out the rest of the query:

SELECT username, posts
FROM vb_user as user
WHERE user.avatarid = '0' AND 
    NOT EXISTS (SELECT * FROM vb_customavatar AS custom WHERE custom.userid = user.userid) AND
	EXISTS (SELECT * FROM vb_customprofilepic AS profile WHERE profile.userid = user.userid)
ORDER BY username asc

[kh99]

I'm glad you got it working. I'm not an SQL expert or anything, and I would never have thought of doing it that way. I would probably have tried JOINing both the customavatar and customprofilepic tables, then checking for the resulting columns being NULL (which would happen if the condition you JOIN on doesn't exist). I'd be interested in knowing if there's any difference (in performance or otherwise) between those two methods.