[thelist] what kind of join is this?

[Emma Jane Hogbin]

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