vb.org Archive - MySQL for finding members with profile pics but not avatars

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - MySQL for finding members with profile pics but not avatars (https://vborg.vbsupport.ru/showthread.php?t=272871)

Soliloquy

10-26-2011 09:01 PM

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;

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	10-26-2011 10:56 PM

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

Soliloquy

10-27-2011 04:47 AM

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

kh99	10-27-2011 06:23 AM

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.

All times are GMT. The time now is 03:50 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01165 seconds Memory Usage 1,715KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: