Trying to select from pn_users (related to pn_group_membership) all users that are members of pn_gid = 1 (users) that ARE NOT a member of pn_gid = 3 (company).
What I'm doing is seperating the member's list into two sections - one for regular users, another for company users.
So, the view function should return all users NOT a member of company group, while a new function, func=company, should return all users that are in the company group.
The func=company SQL is easy pn_gid=3...but having trouble with the Users excluding company group SQL.
Here's some of what I've tried - the coding will be easy once I figure out a query that will work:
Code
SELECT pn_users.pn_uid,pn_users.pn_username FROM pn_users LEFT join pn_group_membership USING (pn_uid) WHERE pn_users.pn_uname NOT LIKE 'Anonymous' AND pn_group_membership.pn_gid = '1' AND pn_group_membership.pn_gid <> '3'
Code
SELECT
nogroup3.pn_uid, nogroup3.pn_uname
FROM
(SELECT pn_users.pn_uid,pn_users.pn_uname, pn_group_membership.pn_gid FROM pn_users LEFT join pn_group_membership ON pn_users.pn_uid=pn_group_membership.pn_uid WHERE pn_group_membership.pn_gid <> '3' ) as nogroup3 WHERE nogroup3.pn_gid = '1'
nogroup3.pn_uid, nogroup3.pn_uname
FROM
(SELECT pn_users.pn_uid,pn_users.pn_uname, pn_group_membership.pn_gid FROM pn_users LEFT join pn_group_membership ON pn_users.pn_uid=pn_group_membership.pn_uid WHERE pn_group_membership.pn_gid <> '3' ) as nogroup3 WHERE nogroup3.pn_gid = '1'
I've also tried a few union combinations, and using different ON statements within the JOIN.
The problem is, whatever I do returns multiple rows instances of the same user - their UID combined with ALL their unique GID's.
ie
row User Group
1 Fred 1
2 Wilma 1
3 Barney 1
4 Fred 3
5 Barney 3
Then, row 1,2 and 3 pass the WHERE clause (pn_gid=1), 4 and 5 are dropped, but Fred and Barney are members of group 3, but their group 1 instance keeps them a part of the found set. Wilma is the only user that should pass the test - member of 1, yet not a member of 3.
I've tried a self join with the group table first, but again, it just returns multiple instances.
The join and subqueries create a row for each group a member belongs to, so the group 3 row instance is dropped...but the row 1 instance is not. So, a member of group 1 AND 3 shows up even though I'm trying to exclude them.
So, how to write a MySQL query that will check ALL groups a unique user belongs to, then only select those that are members of 1, and not 3?
Otherwise, I'll just have to return all rows and do the sort in the PHP.
Thanks for any help from a MySQL guru out there!
NCM
UHEweb
