vb.org Archive

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 01:02 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01046 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