I have a table t containing games. Each game has a winner_id and loser_id.
What I want in the end is a table showing each player_id, number of wins,
and number of loses.
Here's table t:
winner_id | loser_id
----------+---------
1 | 2
1 | 2
1 | 3
2 | 1
2 | 3
select winner_id as id, count(*) as wins from t group by winner_id
gives me table p:
id | wins
---+-----
1 | 3
2 | 2
select loser_id as id, count(*) as loses from t group by loser_id
gives me table q:
id | loses
---+------
1 | 1
2 | 2
3 | 2
select id, wins, loses from p full outer join q on p.id=q.id
gives me table r:
id | wins | loses
---+------+------
1 | 3 | 1
2 | 2 | 2
3 | null | 2
Which is what I want...except mysql 5 doesn't support outer join!
The above query with inner join would get me
id | wins | loses
---+------+------
1 | 3 | 1
2 | 2 | 2
and NOT include player with id=3 because he hasn't both won AND lost
some games (he's only lost so far).
I got around it by doing this:
select p.id, p.wins, q.loses from
(select winner_id as id, count(*) as wins from t group by winner_id) p
left outer join
(select loser_id as id, count(*) as loses from t group by loser_id) q
on p.id=q.id
union
select p.id, p.wins, q.loses from
(select winner_id as id, count(*) as wins from t group by winner_id) p
right outer join
(select loser_id as id, count(*) as loses from t group by loser_id) q
on p.id=q.id
Yeah you read that right: (p LEFT outer join q) union (p RIGHT outer join q)
And it works.
And I feel like a dirtbag.
Tell me there's a better way to do this, WITHOUT using outer joins since MySQL 5 doesn't have them, and WITHOUT saying "you should have been using postgres from the get-go".