Mysql many to many problem (leaderborad/scoreboard)
- by zoko2902
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 :).
