Mysql many, many problems (leaderborad / scoreboard)
I am working on a small project for the upcoming World Cup. I am building a roster / leaderboard / scoring table based on groups with national teams. The idea is to have information about all upcoming matches in the group or during the knockout stage (points, match time, match statistics, etc.). I am currently stuck with DB in that I cannot come up with a query that will return paired commands 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 have simplified the tables so that we don't go in the wrong direction. Now I have tried all kinds of joins and groupings I could think of, but I never get it.
Example request:
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 I really want:
wc_team_id wc_match_id id name id name
16 5 16 Brazil 18 Argentina
Keep in mind that the group has more matches, I want to see that all these matches are not just one.
Any pointer or suggestion would be much appreciated as I am stuck like a duck on this :).
a source to share
Since a football match always consists of two teams, never more and no less, and the order also matters, since there are team 1 and team 2, I would simply add team_1
both team_2
fields to wc_match
and delete the table wc_team_has_match
.
This will greatly simplify your request:
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 store match information in the knockout stages where the teams for each match may not have decided yet. You can still use the same model by making team_1
and team_2
valid. In this case, you want to use LEFT JOINs
instead INNER JOINs
to get a result set that includes commands NULL
. The reason NULL
in SQL is to identify unknown information and hence is ideal for this case.
a source to share
I suggest changing the table team_has_match
to teams_for_match
, which contains a link for each command:
CREATE TABLE `teams_for_match` (
`match_id` INT,
`team1_id` INT,
`team2_id` INT,
PRIMARY KEY (`match_id`, `team1_id`, `team2_id`)
);
you can choose both commands to match:
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.
ps. In addition, you can simply add the columns team1_id
and team2_id
a table wc_team
, do not 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`)
)
a source to share