SQL query to check payed up members

[vlowe]

Hi,

Can anyone advise what sql query would identify all users who are in a 'Members' usergroup who do not have a current 'Active' subscription?

Thanks :)

Vince

[Marco van Herwaarden]

Try the following query:
SELECT u.userid, COUNT(sl.userid) AS subscriptioncount
FROM USER AS u
LEFT JOIN subscriptionlog AS sl ON (u.userid = sl.userid AND sl.status = 1)
WHERE u.usergroupid = 2
GROUP BY u.userid
HAVING subscriptioncount = 0

You will need to replace the usergroupid with the id of the usergroup you are looking for.

I could not test the status field as i don't have any active subscriptions on my testforum, so i am assuming that status = 1 means it is active. You might want to check that in your database first.

[vlowe]

Cheers Marco, that seems to work! :)

Is this only telling me everyone who has an expired subscription who is still a in Member usergroup?

Does it also tell me people in Member usergroup who have no subscription information active or expired?

Cheers

[Marco van Herwaarden]

This should be listing everyone in usergroup = 2 (not taking secondary usergroups into consideration) and have no active subscription (no subscription or only an expired subscription). But as i don't have good real data to test i am not 100% sure.

[vlowe]

Great, yes i thought so after re reading it.

Works perfect cheers :)