Need a MySQL Query

[Falcon Capt]

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.

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]

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

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]

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!!!