Re: n-to-n problem - Bart Locanthi - com.mysql.lists.java

5 messages in com.mysql.lists.javaRe: n-to-n problem

From	Sent On	Attachments
Alessandro Fustini	20 Feb 2002 22:42
jasper fontaine	21 Feb 2002 05:45
Taglang, Guillaume	21 Feb 2002 06:03
Bart Locanthi	21 Feb 2002 07:49
jasper fontaine	23 Feb 2002 03:59

Subject:	Re: n-to-n problem
From:	Bart Locanthi (ba...@sabl.com)
Date:	02/21/2002 07:49:20 AM
List:	com.mysql.lists.java

for a particular user x, you can do this:

select * from users,memberships,groups where users.userid=x and users.userid=memberships.userid and memberships.groupid!=groups.groupid;

if you want to find all users, it's a little more difficult and you have a problem avoiding duplicates.

jasper fontaine wrote:

heya people here is a problem i've been staring at for a while now..

tables (simplified):

- users (userid, username) - memberships (userid, groupid) - groups (groupid, name, desc)

so a user can have multiple memberships, indicating to which groups he belongs.

ok, so far so good. now i want to have a list of all groups that a user can still become a member of (so - all groups to which the user is currently not linked to via a memberships table entry).

I can't seem to make this work. I almost thought i was there, but then i only found a list of groups that other users were members in, not _all_ groups that the user wasn't member in. I know i could make it work by using a subselect (something along the lines of

"select name from groups where not id = (select group_id from memberships where user_id = ?)"

but i understand that mysql doesn't support that?

I'm using mm-mysql-2.0.8 on tomcat4, if that helps ;)

regards, jasper

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail java...@lists.mysql.com instead.