MySQL v5.0.58.
Tables, with foreign key constraints etc and other non-relevant details omitted for brevity:
CREATE TABLE `fixture` (
`id` int(11) NOT NULL auto_increment,
`competition_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`scheduled` datetime default NULL,
`played` datetime default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `result` (
`id` int(11) NOT NULL auto_increment,
`fixture_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`score` int(11) NOT NULL,
`place` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `team` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);
Where:
A draw will set result.place to 0
result.place will otherwise contain an integer representing first place, second place, and so on
The task is to return a string describing the most recently played result in a given competition for a given team. The format should be "def Team X,Team Y" if the given team was victorious, "lost to Team X" if the given team lost, and "drew with Team X" if there was a draw. And yes, in theory there could be more than two teams per fixture (though 1 v 1 will be the most common case).
This works, but feels really inefficient:
SELECT CONCAT(
(SELECT CASE `result`.`place`
WHEN 0 THEN "drew with"
WHEN 1 THEN "def"
ELSE "lost to"
END
FROM `result`
WHERE `result`.`fixture_id` =
(SELECT `fixture`.`id` FROM `fixture`
LEFT JOIN `result` ON `result`.`fixture_id` = `fixture`.`id`
WHERE `fixture`.`competition_id` = 2
AND `result`.`team_id` = 1
ORDER BY `fixture`.`played` DESC
LIMIT 1)
AND `result`.`team_id` = 1),
' ',
(SELECT GROUP_CONCAT(`team`.`name`)
FROM `fixture`
LEFT JOIN `result` ON `result`.`fixture_id` = `fixture`.`id`
LEFT JOIN `team` ON `result`.`team_id` = `team`.`id`
WHERE `fixture`.`id` =
(SELECT `fixture`.`id` FROM `fixture`
LEFT JOIN `result` ON `result`.`fixture_id` = `fixture`.`id`
WHERE `fixture`.`competition_id` = 2
AND `result`.`team_id` = 1
ORDER BY `fixture`.`played` DESC
LIMIT 1)
AND `team`.`id` != 1)
)
Have I missed something really obvious, or should I simply not try to do this in one query? Or does the current difficulty reflect a poor table design?
