vb.org Archive - Need a MySQL Query

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

- vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)

- - Need a MySQL Query (https://vborg.vbsupport.ru/showthread.php?t=311820)

Falcon Capt

05-31-2014 03:03 PM

Need a MySQL Query

I need a MySQL query to search for any members who are in my "Paid" usergroup (Usergroup ID 9) but have no active paid subscriptions (subscription id 1, 9 or 10, either status=0 or NULL)

I am currently using this query but need to run 3 versions of it to catch all the 3 different paid subscriptons.

Code:

SELECT user.username

FROM user

JOIN subscriptionlog sl1 ON 

(user.userid = sl1.userid AND sl1.subscriptionid in (1) AND sl1.status = 0 AND user.usergroupid = 9) 

LEFT JOIN subscriptionlog sl2 ON 

(user.userid = sl2.userid AND sl2.subscriptionid in (9,10))

WHERE sl2.userid IS NULL

Ideally, I'd like to run a single query that will check for all 3 paid subscriptions (either being status=0 or NULL).

Any help that can be provided would be greatly appreciated!

kh99	05-31-2014 11:22 PM

I don't have a real database to test this on, and I'm also not an sql expert, but maybe something like:

Code:

SELECT username FROM 

   (SELECT user.username, SUM(IF (subscriptionlog.subscriptionid IN (1, 9, 10) AND subscriptionlog.status, 1, 0)) AS status_sum

      FROM user

        LEFT JOIN subscriptionlog ON (user.userid = subscriptionlog.userid)

      WHERE user.usergroupid = 9

      GROUP BY user.userid) AS X 

WHERE status_sum = 0

Falcon Capt

06-01-2014 12:15 AM

Ok, that appears to have worked! I'll keep an eye on it and update if it didn't work but so far, so good!

Many, many thanks!!!

All times are GMT. The time now is 10:04 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01055 seconds Memory Usage 1,710KB 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 (2)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (3)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: