The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
||||
|
||||
MySQL for finding members with profile pics but not avatars
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; |
#2
|
|||
|
|||
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.
Code:
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 |
#3
|
||||
|
||||
Thanks kh99, from that I was able to figure out the rest of the query:
Code:
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 |
#4
|
|||
|
|||
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.
|
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|