1

I have a players table

id  name
--  ----
1   jack
2   john
3   mike
4   pikachu

And I have a matches table where these players can be matched together.

id pair_id player_id
-- ------- ---------
 1       1         1
 2       1         2
 3       2         3
 4       2         4

So I tried something like select group_concat(name) group by pair_id The end results is something like:

names
---------
Jack,John

But what I like to is to get players in their own columns like:

p1   p2
---- ----
Jack John

How can I do that?

  • Search for 'pivot'. – Rick James Aug 22 '15 at 2:55
1

No group_concat needed, just joins:

select player1.name as p1, player2.name as p2
from matches m1
join players player1 on m1.player_id = player1.id
join matches m2 on m1.pair_id = m2.pair_id and m1.id != m2.id
join players player2 on m2.player_id = player2.id;
1

Probably you do not want group_concat() at all, you want a join:

SELECT t1.name AS p1, t2.name AS p2
FROM
    players AS t1
INNER JOIN
    matches m1 ON t1.id = m1.player_id
INNER JOIN
    matches m2 ON m1.pair_id = m2.pair_id and m1.id < m2.id
INNER JOIN
    players t2 ON t2.id = m2.player_id

The m1.id < m2.id part ensures that each pair will be returned only once, with p1 being the one with smaller id in the matches table for a given pair_id.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.