Avoiding repeated subqueries when 'WITH' is unavailable

Question

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?

Answer 1

Try selecting the data you need (the team names and place for the target team), then combining that. For two teams per fixture:

SELECT CASE `recent`.`place` 
        WHEN 0 THEN CONCAT("drew with ", other_name)
        WHEN 1 THEN CONCAT("def ", other_name, ", ", targ_name)
        ELSE CONCAT("lost to ", other_name)
    END
FROM (SELECT rtarg.place, targ.name AS targ_name, other.name AS other_name
        FROM `fixture`
        JOIN `result` AS rtarg ON `rtarg`.`fixture_id` = `fixture`.`id`
        JOIN `team` AS targ ON `rtarg`.`team_id` = `targ`.`id`
        JOIN `result` AS rother ON `rother`.`fixture_id` = `fixture`.`id`
        JOIN `team` AS other ON `rother`.`team_id` = `other`.`id`
        WHERE `fixture`.`competition_id` = 2
          AND `rtarg`.`team_id` = @targ
          AND `rother`.`team_id` != @targ
        ORDER BY `fixture`.`played` DESC
        LIMIT 1) AS `recent`;

Handling more than two teams per fixture can be done with minimal alteration, though other subqueries would also work.

SELECT CASE `recent`.`place` 
        WHEN 0 THEN CONCAT("drew with ", other_names)
        WHEN 1 THEN CONCAT("def ", other_names, "; ", targ_name)
        ELSE CONCAT("lost to ", other_names)
    END
FROM (SELECT rtarg.place, targ.name AS targ_name, GROUP_CONCAT(other.name SEPARATOR ', ') AS other_names
        FROM `fixture`
        JOIN `result` AS rtarg ON `rtarg`.`fixture_id` = `fixture`.`id`
        JOIN `team` AS targ ON `rtarg`.`team_id` = `targ`.`id`
        JOIN `result` AS rother ON `rother`.`fixture_id` = `fixture`.`id`
        JOIN `team` AS other ON `rother`.`team_id` = `other`.`id`
        WHERE `fixture`.`competition_id` = 2
          AND `targ`.`id` = @targ
          AND `rother`.`team_id` != @targ
          AND ((rtarg.place<=1 AND rother.place >= rtarg.place)
            OR (rtarg.place>1 AND rother.place < rtarg.place))
        GROUP BY fixture.id
        ORDER BY `fixture`.`played` DESC
        LIMIT 1
) AS recent;

The result format for more than two teams per fixture wasn't specified, so further tweaking may be in order.