How define select, if bridge pair may have 3 players? Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.

Table, members:


pair, player_id

1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 13


table players:


id name

1 Smith
2 Brown
3 Johnson
4 Wild
5 Hammer
6 Bolt
7 Pink
8 Bush
9 Novak
10 King
11 Holmes
12 Watson
13 Gold


Required result of select is:


Pair, Names
1, Smith - Brown
2, Johnson - Wild
3, Hammer - Bolt
4, Pink - Bush
5, Novak - King
6, Holmes - Watson - Gold
This is the MySQL solution (very similar to Leigh's Oracle solution):

SELECT Pair
 , GROUP_CONCAT(name SEPARATOR ' - ') Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;


Obligatory sqlfiddle which was modified from Leigh's illustration as well.*

* I give credit when I plagiarize!