Question

我有一个数据库,两个表格和一个链接表,我需要一个联合网络查询:

我的表格如下:

family (userid (int), loginName, etc)
member (memberid (int), loginName(this links member to a family),name, etc)

Linking Table: user2member (userid,memberid)...would both be foreign keys?

我想谈两点:

(1) 能够有一个家庭:loginName(12,Johnson)为另一个家庭。标识(43,Smith)并记录在连接表中。

That would look like this: 12,43

2) 当我向所有成员提问时。在Johnson家庭的名字,我获得所有Johnsons &所有Smiths。

If Johnson = Ted, Sue & Patty IF Smith =Joe, Sue & Bob

我的询问结果为:Johnson现在=Ted,Sue,Patty,Joe,Sue,Bob

我在几天前没有很好的表格名称就询问了这个问题,最后我把自己和 n笑·奥利·琼斯混为一谈。

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在5行的差错只有0,30个。

这是我第一次做联合调查组的工作,如果这样做是正确的,我没有任何想法。

感谢

这里与我第一个问题的联系:mySQL tablelinking , group associated with other members list, the showing all members

Answer 1

我确信,如果你所建议的表格能够解决你的问题。如果我理解你的问题正确的话,就有两个关系:

a relationship for all family members (Johnson with Ted, Sue, Patty, Smith with Joe, Sue, Bob)
a relationship for subscriptions (a family can subscribe to another family)

我提出以下表格:

family (f_id, f_loginName, etc.)
member (m_id, m_f_id, m_name) (foreign key to family, many-to-one relationship)
subscription (s_f_id,s_to_f_id) (linking is based on both family keys)

这将产生以下内容:

family:
f_id   f_loginName
12     Johnson
43     Smith

member:
m_id   m_f_id   m_name
1      12       Ted
2      12       Sue
3      12       Patty
4      43       Joe
5      43       Sue
6      43       Bob

subscription
s_f_id s_to_f_id
12     43

现在,为了让所有可能的成员都参加特定家庭并订阅,我将在问询后使用。它仅与家人团聚。在WHERE条款中,家庭Johnson(f_id = 12)是固定的,而且要让所有家庭成员都从订阅中受益,因此更容易使用分局。

SELECT f_loginName, m_name 
FROM family
INNER JOIN member ON m_f_id = f_id
WHERE f_id = 12 
      OR f_id IN (SELECT s_to_f_id FROM subscription WHERE s_f_id = 12) 
ORDER BY f_loginName, m_name;

友情链接