mysql Select question

Question

hi There,

I can't get on the right track with this, any help would be appreciated

I have 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

Answer 1

I think you should redesign your table though, maybe the format that you want as output should be your table design.

With your design, it's possible to have three or more teams playing against each other...

---

So. I gave this another try (coming from Oracle myself, I really miss ROWNUM here).

The following query should give you the result you want to have, but I'm not sure if you should really do that in pure SQL. Maybe you could just combine the teams in your client?

SELECT m1.match_id, m1.team_id, m2.team_id, m1.player_id, m2.player_id
FROM (
  SELECT match_id, team_id, player_id,
   -- get ranking
   ( SELECT 1 + count(*)
     FROM   matches m1b
     WHERE  m1b.match_id = m1a.match_id
     AND    m1b.team_id = m1a.team_id
     AND    m1b.player_id < m1a.player_id) rank
  FROM matches m1a
  WHERE m1a.team_id = (SELECT MIN(team_id) -- first team
                       FROM    matches
                       WHERE match_id = m1a.match_id)
)  m1,
(
  SELECT match_id, team_id, player_id,
   -- get ranking
   ( SELECT 1 + count(*)
     FROM   matches m2b
     WHERE  m2b.match_id = m2a.match_id
     AND    m2b.team_id = m2a.team_id
     AND    m2b.player_id < m2a.player_id) rank
  FROM matches m2a
  WHERE m2a.team_id = (SELECT MAX(team_id) -- second team
                        FROM matches
                        WHERE match_id = m2a.match_id)
)  m2
WHERE m1.match_id = m2.match_id
AND   m1.rank = m2.rank

What I do here is:

1. Select all ROWs from the teams with lower team_id per match and give them a ranking (1 to 3 per match)
2. Select all ROWs from the teams with higher team_id per match and give them a ranking (1 to 3 per match)
3. Combine those two queries in one result, where the match_id and the ranking match

Answer 2

match is a reserve word in mysql. table name used here is matchs

select match_id, sum(if(id=1, team_id,0))team_A, sum(if(id=2,team_id,0)) team_b 
from matchs 
group by match_id;
+----------+--------+--------+
| match_id | team_A | team_b |
+----------+--------+--------+
|        5 |      9 |     10 |
+----------+--------+--------+
1 row in set (0.00 sec)

Answer 3

I'm not sure if the previous answers will give you what you're looking for, at least I took your question to mean something else - perhaps you could clarify the purpose of the table and the query. If the table associates teams with matches and you want a query to show you all the teams associated with one match, then your query should be

select team_id as teams from table where match_id = id_here

which would give you back (for id_here being 5)

teams
-----
9
10

Answer 4

Take a look at the url below, It is exactly what you want but is in t-sql. It can merge any number of rows.

• Converting fields into columns