MySQL Question [CLOSED]

Hello

I am sorry to ask for a probably obvious SQL problem...

On my site I use several joomla users groups :
- all registered users are in group '2'
- all paid users are in group '3' AND in group '2' : the CBSubs plans move them to groupe '3' all time plan is active
- some advanced members are in group '2', '3' and higher groups

So paid users have at least two rows in the #__user_usergroup_map table (group '2' and group '3').

---

To get paid members I can use some SQL SELECT Query using .

---

I would like to select users who are ONLY in group '2' (and NOT in '3' ou higher). If I use I get all users because all users are also in group2.

I am sure it is a very common SQL question but I cannot write it correctly. If you could help it would be fantastic !

Best regards

Antoine
Antoine

Why are you needing to query that database directly? To query for subscribed users you should be querying against _cbsubs_subscriptions database table.

Please keep in mind we do not provide coding assistance here. That table will have 1 row per user per group assigned to them. While it's doable to do what you're wanting it's not efficient query wise. That table grows quite exponentially compared to _cbsubs_subscriptions which would be significantly faster and easier to query. For example the below query gives you user ids subscribed to a specific plan.


Replace PLAN_ID_HERE with the plan you want to check for. Remove that condition entirely to get all users with an active subscription to any plan or change it to an IN statement to check against a list of plan ids.

Thanx for your answer, and sorry for my question. It is more a logical question about CBsubs than a coding problem.

Previously we were using CBuilder and manual payement, and since january we are using CBsubs. So we have
- registered users without any subscription (as they paid manually last year but still didn't repay this year)
- paid users who have a subscription not Active (expired or cancelled)
and I cannot get users without any subscription with your query. That is the reason why I wanted to use Joomla users groups.

Next year our database will be full of users having a subscription history so it won't be a problem to use cbsubs_subscriptions table, but this first year it is a bit tricky.

Regards,
Antoine

There isn't going to be an efficient way to query for that information. This is due to users having multiple rows in that table and you're needing filter against those other rows. The only way to do that is with a subquery, which will be slow. Is this a 1 time thing just to export data or are you needing this to run regularly on frontend?

Suggest looking into using NOT EXISTS or NOT IN subquery, but best performance will probably be a LEFT JOIN subquery. Regardless the performance won't be good. The below for example will work, but again it will be slow.


Replace GROUP_TO_EXCLUDE with the id of the usergroup to exclude from the list.

GREAT ! Thanx a lot.

I tried a sub select query but didn't use it with NOT EXISTS, which is exactly what I needed.
With your help I wrote what I needed. Thanx a lot.

It is not for frontend purpose, but for a very few times backend management during this transition year. So I don't really care about SQL performance for a one shot (maybe twenty shots max in a year) query.

Next year I will use only CBSubs subscriptions and it will be easier.

Again, many thanks for your help, as usual.

Regards
Antoine