[thelist] what kind of join is this?
Emma Jane Hogbin
emmajane at xtrinsic.com
Wed Jul 23 17:38:12 CDT 2003
I'm trying to select a number of users from a series of (MySQL) tables under one
of two conditions:
(1) they do /not/ appear in the group_membership table for a specific
group (add users to the group)
(2) they do appear in the group_membership table for a specific group
(change the permissions/remove existing users)
select login.logid, login.first, login.last, group_roles.name, group_membership.role
from group_membership
left join login on login.logid = group_membership.logid
left join group_roles on group_membership.role = group_roles.id
where group_membership.groupid != 10 ** this line changes
order by login.last
But this gives me a list of every group the user is in. So I think I
should (1) be using a different join or (2) be using a group by statement.
I can't seem to find the right combination though. If I group by
login.logid or group_membership.logid I get the wrong group_membership
role reported back (and it implies that someone is NOT a member of a group
when they actually are).
I want it to give me a list of: all users in the group; OR all users not
in the group. The role says, "this is a group leader, group member, or
someone who has requested membership but is not yet a member". I'm not
sure if it's the join I'm getting wrong or the group-by.
Thoughts on what I'm missing?
emma
mysql> select * from group_roles;
+----+--------------------+-------+--------+
| id | name | about | groups |
+----+--------------------+-------+--------+
| 1 | Membership pending | NULL | NULL |
| 2 | Private group | NULL | NULL |
| 3 | Owner | NULL | NULL |
| 4 | Group member | NULL | NULL |
+----+--------------------+-------+--------+
mysql> describe group_membership;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| logid | int(11) | | | 0 | |
| groupid | int(11) | | | 0 | |
| role | tinyint(4) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
mysql> describe login;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| logid | int(11) | | PRI | NULL | auto_increment |
| last | varchar(50) | YES | | NULL | |
| first | varchar(50) | YES | | NULL | |
| middle | varchar(50) | YES | | NULL | |
<snip>
+------------+--------------+------+-----+---------+----------------+
--
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]
More information about the thelist
mailing list