mySQL表链接，组链接到其他成员列表，显示所有成员 [英] mySQL table linking , group linked to other members lists, the displaying all members

问题描述

我有一个本身具有 group（g1）和 members（m1）的列表的数据库

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

但是我想添加 group（g1）的功能来添加不同的 groups（g2-3）到列表，以便查询将显示所有 members（M1）+（m2-3）

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.

我的表是：

Group(1) Table:  Group1, 
Member(2) table: MemberA, MemberB, MemberC etc.

我认为我需要某种类型的链接表，其中 group（1）输入要订阅 Group（2）成员。

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

我想链接表如下：

GroupID,  Group Name,GroupID, GROUP subscribed to Name 
Group(1), FancyGroup(1), Group(2), shabby Group(2) 

这就是我想要的 Group（1）查询的结果订阅 Gr之后oup（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?

更新3/9：

这些是我的表名：

该组称为家庭;行是（ userid，loginName .. etc）

成员组称为 member ;行是（ memberid，loginName，name，等）

Join表称为 user2member ;行是（ userid，memberid ）。

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

我开始出现此错误： 〜...在第5行的'WHERE family.userid ='30'ORDER BY member.name LIMIT 0，30'附近使用的语法

想法？

推荐答案

我认为您正在尝试代表组与成员之间的多对多关系。

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

为此，您需要一个组表，每个组都有一行。
GroupID，GroupName，等等。

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

您需要一个成员表，每个人一行。
MemberID，名字，姓氏，等等。

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

然后，您需要一个联接表，一个groupmembership表。最简单的组成员资格表具有这样的行

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

MemberID，GroupID

MemberID, GroupID

每个组每个成员一行。如果您的应用程序需要它们，则可以在此表中添加其他内容，例如DateJoined或ActiveMembership或其他任何内容。

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

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.

这篇关于mySQL表链接，组链接到其他成员列表，显示所有成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！