I can't get on the right track with this, any help would be appreciated
I hav开发者_StackOverflow社区e one table
+---+----------+---------+-----------+
|id | match_id | team_id | player_id |
+---+----------+---------+-----------+
| 1 | 9 | 10 | 5 |
| 2 | 9 | 10 | 7 |
| 3 | 9 | 10 | 9 |
| 4 | 9 | 11 | 12 |
| 5 | 9 | 11 | 15 |
| 6 | 9 | 11 | 18 |
+---+----------+---------+-----------+
I want to select these with a where on the match_id and both team id's so the output will be
+---------+-------+------+---------+---------+
| MATCHID | TEAMA | TEAMB| PLAYERA | PLAYERB |
+---------+-------+------+---------+---------+
| 9 | 10 | 11 | 5 | 12 |
| 9 | 10 | 11 | 7 | 15 |
| 9 | 10 | 11 | 9 | 18 |
+---------+-------+------+---------+---------+
It's probably very simple, but i'm stuck..
thanks in advance
p.s. seemed to forgot a column on my first post, sorry
I think you need:
SELECT
a.match_id, a.team_id AS TeamA, b.team_id AS teamB,
a.player_id AS PlayerA, b.player_id AS PlayerB
FROM PLayer AS a
INNER JOIN Player AS b ON a.match_id = b.match_id
WHERE a.team_id < b.team_id
Though this will give you every pair of players for each game, i.e.
+---------+-------+------+---------+---------+
| MATCHID | TEAMA | TEAMB| PLAYERA | PLAYERB |
+---------+-------+------+---------+---------+
| 9 | 10 | 11 | 5 | 12 |
| 9 | 10 | 11 | 5 | 15 |
| 9 | 10 | 11 | 5 | 18 |
| 9 | 10 | 11 | 7 | 12 |
| 9 | 10 | 11 | 7 | 15 |
| 9 | 10 | 11 | 7 | 18 |
| 9 | 10 | 11 | 9 | 12 |
| 9 | 10 | 11 | 9 | 15 |
| 9 | 10 | 11 | 9 | 18 |
+---------+-------+------+---------+---------+
To restrict it further, you need a criterion to determine players should be paired.
I think it's better to redesign your database.
select MatchA.id as MATCHID, MatchA.team_id as TEAMA, MatchB.team_id as TEAMB, MatchA.player_id as PLAYERA, MatchB.player_id as PLAYERB
from Match as MatchA, match as MatchB
where MatchA.id = MatchB.id and MatchA.team_id < MatchB.team_id
精彩评论