Query syntax help needed
 

I'm trying to create a list of my Registered Users (primary usergroup #2)that are NOT also in secondary group #12. I've tried this.. and a few variations thereof, but I haven't hit on the magic combination that excludes the members of group #12.

I don't know if it is germane, but users CAN be in more than one secondary group on my board, so the membergroupids entry for a user could be a series of group numbers strung together with commas, like "8,9,12,13".

I'd greatly appreciate it if somebody could help with the correct query syntax.

SELECT email, username
FROM user
WHERE usergroupid =2
AND membergroupids !=12

or perhaps if you have multiple groups:

SELECT u.email, u.username FROM user u
WHERE u.usergroupid=2
and u.userid != (select userid v from user v where v.membergroupids LIKE '%12%' and u.userid = v.userid)

Thanks, this is close. It eliminates those that are ONLY in secondary group 12 (i.e. those where membergroupids = "12" but does not eliminate those that are in both groups 12 and 13 (i.e. membergroupids = "12,13") and maybe some other combinations that I haven't checked.

We need, not an inequality but a "not found in" check.

[Edit] I see you edited yours while I was writing the above reply. I'll try your additional suggestion and get back...

Sadly, no go. Doesn't return anybody.

But combining the two approaches, it looks, on first blush, like this does work:

SELECT email, username
FROM user
WHERE usergroupid =2
AND membergroupids NOT LIKE '%12%'

I'll have to spot check a few users of known multiple group memberships, but so far I think that'll do it. Thanks for your help.

..AND NOT FIND_IN_SET(12, membergroupids)...

Have already created the list using NOT LIKE ...
.. but will add this to my bag o' tricks for next time.

Thanks, Marco!

You can not do the trick with a (NOT) LIKE.

Example:

Member A, Membergroupids: '1,4,123'
Member B, Membergroupids: '12,31'

Looking for '%4%' would give you the correct result, but using '%12%' would also match the '123', and that is not what you want.

Thanks again, Marco. As it turns out in my particular scenario checking for '%12%' worked okay because we don't have any groups with 3-digit numbers. But it is definitely something to keep in mind when checking for single-digit group numbers.