mySQL table linking , group linked to other members lists, the displaying all members

Question

I have a database with a group(g1) and a list of members(m1), by itself it works fine.

However I want to add the ability of the group(g1) to add different groups(g2-3) to it's list so that a query would bring up ALL the members(M1)+(m2-3) as a result.

My tables are:

Group(1) Table: Group1,

Member(2) table: MemberA, MemberB, MemberC etc.

I think I need some type of linking table, where group(1) inputs that it wants to subscribe to a Group(2) member.

I was thinking the linking table would look like:

GroupID, Group Name,GroupID, GROUP subscribed to Name

Group(1), FancyGroup(1), Group(2), shabby Group(2)

This is what I want the results for Group(1) query to look like after it subscribe to Group(2):

Fancy Group Fancy MemberA Fancy MemberB Fancy MemberC Shabby MemberA Shabby Member B

Any Ideas? I realize this is a long question but I didn't know a shorter way of righting it?

Thanks,

Michael

UPDATE 3/9:

These are my table names:

The Group is called "family" ; Rows are (userid,loginName..etc)

The Member group is called "member" ; rows are (memberid,loginName,name, etc)

The Join table is called "user2member" ; rows are (userid,memberid) .

This is what I am using for the query:

SELECT member.name   FROM family JOIN user2member on family.userid = member.memberid JOIN member on user2member.name = member.name   WHERE family.userid = '30' ORDER BY member.name

I'm gettin this error:~...syntax to use near 'Â WHERE family.userid = '30' ORDER BY member.name LIMIT 0, 30' at line 5

Ideas?

Answer 1

I think you're trying to represent a many-to-many relationship between groups and members.

To do this, you need a group table with a row for each group. GroupID, GroupName, whatever, whatever.

You need a member table with a row for each person. MemberID, Firstname, Lastname, whatever, whatever.

You then need a join table, a groupmembership table. The simplest group membership table has rows like this

MemberID, GroupID

It has one row per member per group. You could add other things to this table if your application needed them, such as DateJoined or ActiveMembership, or whatever.

Then, you'd use joins to get your data back. If you wanted a list of members in the "geeks" group, you'd use a join like this.

SELECT m.Firstname, M.Lastname
  FROM group g
  JOIN groupmembership gm on g.GroupID = gm.GroupID
  JOIN member m on gm.MemberID = m.MemberID
 WHERE g.GroupName = 'geeks'
ORDER BY M.Lastname, M.Firstname

If you wanted a list of members not belonging to any group, you'd do this.

SELECT m.Firstname, M.Lastname
  FROM member m
  LEFT JOIN groupmembership gm on m.MemberID = gm.MemberID
 WHERE gm.GroupID IS NULL
ORDER BY M.Lastname, M.Firstname

It's a super-useful design pattern. Good luck.