你能帮我制定一个SQL查询来加入3个表吗？ [关闭]

Question

I'm not sure how to do this so I'm asking it here...

I have 3 tables in my database with structure like this..

table:contact ----- rows: contact_id, firstname

table:groups ------rows: group_id, group_name

table:groupmembership ------ rows: contact_id, group_id

The association between between "contact_id" and "group_id" in the table "group_membership" reflects a contact that is in a particular group.

What I need to do is take a contact_id, and display which groups he is a member of in a dropdown list. I'll eventually use this dropdown list to post the group_id to a page that diplays the group info.

Can anyone help me with how to formulate the MySQL query?

Answer 1

If you want to select by user_id:

SELECT group_name, group_id
FROM groupmembership gm
INNER JOIN groups g ON g.group_id = gm.group_id
WHERE gm.contact_id = 4711

If you want to select by user's firstname:

SELECT group_name, group_id
FROM groupmembership gm
INNER JOIN groups g ON g.group_id = gm.group_id
INNER JOIN contact c ON c.contact_id = gm.contact_id
WHERE c.firstname LIKE 'TheHe';