mySQL表链接，组链接到其他成员列表，显示所有成员

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?

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.