MyBB Community Forums

Ok so we're about to make some changes to how our user groups work to try and simplify years of too many options

I'm trying to come up with a way to query various current group types to find any outliers who in the past where potentially left with multiple groups. I'd like to fix these before we make these blanket changes. At the moment I'm simply trying to find any members who have our base level as their primary group and then anything at all in additionalgroups. My SQL knowledge is mediocre at best and when we get into multiple tables I get more twisted than carnival funnel cakes.  Here's where I am at the moment:

SELECT 
u.username as Member,
u.usergroup as priGroup,
u.additionalgroups as othrGroups,
u.displaygroup as displayGroup,
ug.title as Status,
uf.fid5 as Realm

From mybb_users u,
mybb_userfields uf,
mybb_usergroups ug

where u.uid = uf.ufid
and u.usergroup = ug.gid
and u.usergroup in (8)
and u.additionalgroups is not NULL

Order BY
ug.title,
u.username

Again, the goal for this example is that I get a list of users who are part of group 8 but also have data in the additionalgroups column. The above is giving me all members who are in group 8. I see hundreds that have nothing in the addtionalgroups column of the user table.

Anyone able to nudge me in the right direction?

Thanks,
gmO

It should be this:

SELECT username, usergroup, additionalgroups FROM mybb_users WHERE usergroup=8 AND additionalgroups != '' ORDER BY username ASC;

I could do without the info from the other tables in a pinch but even that query is returning results that aren't true... at least not in the sense of what I see in the tables. Specifically, I've got some users who have multiple group ids in the additiongroups column and they don't show up. But this got me much closer

---

It is even more than that... oddly it's leaving some out even if there is only one group in additional groups. Here's a portion of a full dump of users with gid 8 sorted in excel where additional groups has data:

Name pri additional display
name1 8 39 0
name2 8 39 0
name3 8 39 0
name4 8 39 0
name5 8 32 0
name6 8 28,39 0
name7 8 28 0
name8 8 20 0
name9 8 15 0
name10 8 15 0

And in the results from the above query, names 1, 2, 3, 4, 8, 9, and 10 are the only ones that come out.