Mysql Query Relations M-M Table

I'm having a small problem making a query in MySQL. I have the following tables:

• member;
• group;
• member_has_group (this one has the columns id_group referes to the group id and id_member referes to member id)

I'm trying to make a query that gives me the members from a selected group. Can you help me?

开发者_StackOverflowI'm not familiar with join tables, but for the search i made i think thats probably one of the solutions.

Thanks in advance.

Elkas

If you know the group id

select member.* from member m
  inner join member_has_group mg on m.id = mg.id_member
where mg.id_group = [x]

If you only know the group name

select member.* from member m
  inner join member_has_group mg on m.id = mg.id_member
  inner join group g on g.id = mg.id_group
where g.name = 'group name'

This is trival in SQL :

SELECT m.id_member, m.name
FROM member AS m
INNER JOIN member_has_group AS h ON (m.id_member=h.id_member)
WHERE (h.id_group=@my_id_group)

@my_id_group is the group id you have to give.

Yep, you need a join here.

SELECT *
FROM `member` 
JOIN `group` ON member.id = group.id
JOIN `member_has_group` ON group.id = member_has_group.id

Depending on the information in your tables, you may not need the third table at all.You only need a connector table with you have a "many to many" relationship between then.

(Ignore the rest if you already know about database normalization)

For example, if you had two tables, Authors and Books. Authors would contain fields such as Name, Publisher, Birthday, whatever is a property of the "author". Books would contain relevant "book" information. This is a "one-to-many" relationship. An author may be linked (via a field such as author_id) to several books, but a book can only have one author. You would not need a third table here.

Building on that, say you had a third table for "Character Names". This would be a list of main character names used in any of the books in the "Books" table. One of the characters happens to be named John Steele. John has a whole series of books written about him. In the Books table, several of the books may list John Steele as a character. While in the characters table, John Steele could be listed in several books. This is "many-to-many". You need a third table here. It would only have two fields. A book_id and character_id, one entry for each book that John Steele appears in.

MySql Manual on DB Normalization