Matching multiple row data from one table with a single row of another table

[KitCarl]

Reading the responses to questions on this forum and having a few posts answered has been a fantastic learning tool! I still know almost nothing but I'm making more progress than I was from the manuals. After reading several posts and experimenting I had a big  after figuring out how to left join multiple tables together create a list from more normalized tables. Anyhow that works fine if I want to create a list of people who belong to each club, but not if I want to list the people and which club(s) they are members.

 

In simple terms:

I have Person table P with P.personid, P.first name, etc.

I have Club table C with C.clubid, C.name, etc.

I have clubMember table CM with CM.clubMemberID, CM.personid , CM.clubID

 

Each person can belong to more than one club so how do I write a query to give me the Name info from the Person table once, but return all C.name's that match the P.personID so I can display it as

 

Name                                      Clubs

Joe Whatever                                Club A, Club B, Club C

 

NOT

 

Name                                            Clubs

 

Joe Whatever                                              Club A

Joe Whatever                                              Club B

Joe Whatever                                              Club C

 

Do I need a sub-query or is this solved with PHP?

[Ken2k7]

I think you want GROUP_CONCAT.

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[KitCarl]

Thank You