[thelist] what kind of join is this?

[Emma Jane Hogbin]

On Wed, Jul 23, 2003 at 06:45:44PM -0400, rudy wrote:
> > I want it to give me a list of:
> >   all users in the group;
> >  OR all users not in the group.
> if you want A OR not A, just select them all   ;o)
> obviously, that's not what you want
> so, perhaps restate your problem?
> also, a few rows of actual sample data might make the job easier to decipher

If the person is in the group: tell me once. If the person is not in the
group: tell me that once. Do NOT tell me all of the groups they are in.

mysql> select group_membership.groupid
	, login.logid
	, login.first
	, login.last
	, group_roles.name
	, group_membership.role 
	from login 
	inner join group_membership on login.logid = group_membership.logid inner 
	join group_roles on group_membership.role = group_roles.id 
	where group_membership.groupid != 10
	order by login.last;

This gives me:
+---------+-------+-----------+----------+---------------+------+
| groupid | logid | first     | last     | name          | role |
+---------+-------+-----------+----------+---------------+------+
|       8 |  1586 | Emma      | Hogbin   | Owner         |    3 |
|       5 |  1586 | Emma      | Hogbin   | Owner         |    3 |
|       4 |  1586 | Emma      | Hogbin   | Private group |    2 |
|       9 |  1586 | Emma      | Hogbin   | Group member  |    4 |
|       1 |  1575 | dominique | scheffel | Private group |    2 |
|       3 |  1575 | dominique | scheffel | Owner         |    3 |
|       5 |  1575 | dominique | scheffel | Owner         |    3 |
+---------+-------+-----------+----------+---------------+------+
7 rows in set (0.00 sec)

OR
	where group_membership.groupid = 10
+---------+-------+-------+--------+-------+------+
| groupid | logid | first | last   | name  | role |
+---------+-------+-------+--------+-------+------+
|      10 |  1586 | Emma  | Hogbin | Owner |    3 |
+---------+-------+-------+--------+-------+------+
1 row in set (0.00 sec)

So you see that "emma" is a member of the group, but she is also a member
of other groups and these other groups are listed when I say, "tell me
everyone who is currently NOT a member of this group."

If I add a "group by" qualifier I still get "emma" listed in the first
list of all the people who are NOT members, which is wrong. I've tried to
group by login.logid and group_membership.logid (which is the same thing).

Hopefully that makes more sense.


-- 
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]