Many to Many Relationship using Joins

Question

I have a database two tables and a linking table that I need a JOIN query for:

Here are my Tables:

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?

I want to do two things:

1) Be able to have a family.loginName(12,Johnson) subscribe to another family.loginName (43,Smith) and record that into the linking table.

That would look like this: 12,43

2) When I do a query for all the member.name that are in the Johnson Family, I'll get all the Johnsons & all the Smiths.

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

my query results would be Johnson now = Ted,Sue,Patty,Joe,Sue,Bob

I asked this question a few days ago without good table names and I ended up confusing myself and the nice guy Ollie Jones who posted an answer similar to this 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 had to change Ollie's answer to match my tables but I'm getting a limit error 0,30 on line 5.

This is my first time doing JOINS and I have no idea if this is correct.

Thanks,

Here's the link to my first question: http://stackoverflow.com/questions/2392913/mysql-table-linking-group-linked-to-other-members-lists-the-displaying-all-mem

Answer 1

I am not sure, if the tables you suggested would solve your problem. If I understand your question correct, there are two relationships:

• 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)

I would suggest following tables:

• 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)

This would result in following contents:

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

Now, to get all possible members for a specific family and it's subscriptions, I would use following SQL query. It has a simple join for family and it's family members. In the WHERE clause, the family Johnson is fetched (f_id = 12) and to get all family members from the subscriptions, it's easier to use a subquery.

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;