Mysql many to many problem (leaderborad/scoreboard)

Question

Hi all! I'm working on a small project in regards of the upcoming World Cup. I'm building a roster/leaderboard/scoredboard based on groups with national teams. The idea is to have information on all upcoming matches within the group or in the knockout phase (scores, time of the match, match stats etc.). Currently I'm stuck with the DB in that I can't come up with a query that would return paired teams in a row. I have these 3 tables:

      CREATE  TABLE IF NOT EXISTS `wc_team` (
      `id` INT NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NULL ,
      `description` VARCHAR(250) NULL ,
      `flag` VARCHAR(45) NULL ,
      `image` VARCHAR(45) NULL ,
      `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
      PRIMARY KEY (`id`) ,

      CREATE  TABLE IF NOT EXISTS `wc_match` (
      `id` INT NOT NULL AUTO_INCREMENT ,
      `score` VARCHAR(6) NULL ,
      `date` DATE NULL ,
      `time` VARCHAR(45) NULL ,
      `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
      PRIMARY KEY (`id`) , 

      CREATE  TABLE IF NOT EXISTS `wc_team_has_match` (
      `wc_team_id` INT NOT NULL ,
      `wc_match_id` INT NOT NULL ,
      PRIMARY KEY (`wc_team_id`, `wc_match_id`) ,

I've simplified the tables so we don't go in the wrong direction. Now I've tried al kinds of joins and groupings I could think of, but I never seem to get. Example guery:

SELECT t.wc_team_id,t.wc_match_id,c.id.c.name,d.id,d.name
FROM wc_team_has_match AS t
LEFT JOIN wc_match AS s ON t.wc_match_id = s.id
LEFT JOIN wc_team AS c ON t.wc_team_id = c.id
LEFT JOIN wc_team AS d ON t.wc_team_id = d.id

Which returns:

 wc_team_id  wc_match_id    id name      id name    
         16           5        16 Brazil    16 Brazil
         18           5        18 Argentina 18 Argentina

But what I really want is:

    wc_team_id  wc_match_id     id name      id name    
             16           5     16 Brazil    18 Argentina

Keep in mind that a group has more matches I want to see all those matches not only one. Any pointer or suggestion would be extremly appreciated since I'm stuck like a duck on this one :).

Answer 1

Since a soccer match is made up of always two teams, never more and never less, and the order is also of significance since there is a team 1 and a team 2, I would simply add team_1 and team_2 fields in wc_match and remove the wc_team_has_match table.

This would simplify your query considerably:

SELECT  m.wc_match_id, 
        t1.id   AS Team_1_ID,
        t1.name AS Team_1,
        t2.id   AS Team_2_ID,
        t2.name AS Team_2
FROM    wc_match AS m
JOIN    wc_team t1 ON (t1.id = m.team_1)
JOIN    wc_team t2 ON (t2.id = m.team_2);

---

EDIT: Just noticed that you also intend to keep information on matches in the knockout stages, where the teams for each match might not have been decided yet. You can still keep with the same model, making team_1 and team_2 nullable. In that case you would want to use LEFT JOINs instead of INNER JOINs in order to receive a resultset that includes NULL teams. The reason for NULL in SQL is to define unknown information, and therefore fits this case perfectly.

Answer 2

i suggest to change your team_has_match table to teams_for_match which holds a reference for each team:

CREATE TABLE `teams_for_match` (
  `match_id` INT,
  `team1_id` INT,
  `team2_id` INT,
  PRIMARY KEY (`match_id`, `team1_id`, `team2_id`)
);

you can then select both teams for a match with:

   SELECT *
     FROM `teams_for_match` tm
LEFT JOIN `wc_match` m
       ON tm.`match_id` = m.id
LEFT JOIN `wc_team` t1
       ON tm.`team1_id` = t1.id
LEFT JOIN `wc_team` t2
       ON tm.`team2_id` = t2.id;

i hope this works as you expect it to.

ps. apart from that, you could simply add team1_id and team2_id columns to your wc_team table, no need to create an additional table

CREATE  TABLE IF NOT EXISTS `wc_match` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `score` VARCHAR(6) NULL ,
  `date` DATE NULL ,
  `time` VARCHAR(45) NULL ,
  `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `team1` INT FOREIGN KEY REFERENCES `wc_team`(`id`),
  `team2` INT FOREIGN KEY REFERENCES `wc_team`(`id`),
  PRIMARY KEY (`id`)
)