Query syntax help needed

[makaiguy]

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

SELECT email, username FROM user WHERE (usergroupid=2 AND NOT(membergroupids=12));

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

[briansol]

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)

[makaiguy]

Originally Posted by briansol

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

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

Originally Posted by briansol

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)

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.

[Marco van Herwaarden]

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

[makaiguy]

Originally Posted by Marco van Herwaarden

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

[Marco van Herwaarden]

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.

[makaiguy]

Originally Posted by Marco van Herwaarden

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.