如何在MariaDB中连接三表替换game表多列外键ID为实际值？

问题描述 #

我拥有conference、game、team三个表，表定义如下：

CREATE TABLE `game` ( `id` int(11) NOT NULL AUTO_INCREMENT, `home_team` int(11) NOT NULL, `away_team` int(11) NOT NULL, `winner` int(11) DEFAULT NULL, `home_conference` int(11) DEFAULT NULL, `away_conference` int(11) DEFAULT NULL, `week` int(5) NOT NULL, `confidence` int(5) NOT NULL, PRIMARY KEY (`id`), KEY `fk_game_home_team` (`home_team`), KEY `fk_game_away_team` (`away_team`), KEY `fk_game_winner` (`winner`), KEY `fk game_home_conference` (`home_conference`), KEY `fk game_away_conference` (`away_conference`), CONSTRAINT `fk game_away_conference` FOREIGN KEY (`away_conference`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION, CONSTRAINT `fk game_home_conference` FOREIGN KEY (`home_conference`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION, CONSTRAINT `fk_game_away_team` FOREIGN KEY (`away_team`) REFERENCES `team` (`id`) ON UPDATE NO ACTION, CONSTRAINT `fk_game_home_team` FOREIGN KEY (`home_team`) REFERENCES `team` (`id`) ON UPDATE NO ACTION, CONSTRAINT `fk_game_winner` FOREIGN KEY (`winner`) REFERENCES `team` (`id`) ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `conference` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
CREATE TABLE `team` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `conference_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_team_conference_conferenceid` (`conference_id`), CONSTRAINT `fk_team_conference_conferenceid` FOREIGN KEY (`conference_id`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=293 DEFAULT CHARSET=utf8;

game表的部分数据如下：

可见game表中home_team、away_team、home_conference、away_conference均为对应表的外键ID，我希望查询所有game数据时将这些ID替换为对应表的实际值。但尝试多次未成功：

• 执行SELECT * FROM game LEFT JOIN team ON game.home_team=team.id;仅能获取主场球队信息；
• 执行SELECT * FROM game LEFT JOIN team ON game.home_team=team.id AND game.away_team=team.id;无结果返回。

我使用的是MariaDB，请问该如何实现需求？

解决方案 #

你的问题核心在于需要多次关联同一个表，并通过表别名区分不同的外键关联——因为game表同时关联了两个球队（主、客）和两个会议（主、客），一次关联只能对应一个外键，不能用同一个表实例同时匹配两个不同的ID。

先分析你之前的错误 #

1. 第一次查询只关联了team表一次，所以只能匹配home_team对应的球队信息，away_team的信息自然无法获取；
2. 第二次查询用AND条件要求同一个team.id同时等于home_team和away_team，除非一场比赛的主客场是同一支球队（几乎不可能），否则必然返回空结果。

正确的SQL语句 #

下面的查询会分别关联team和conference表两次，用别名区分主客场对应的记录，并将所有ID替换为实际名称：

SELECT
    game.id AS game_id,
    home_t.name AS home_team_name,
    away_t.name AS away_team_name,
    -- 处理winner字段，显示获胜球队名称（如果有结果）
    CASE 
        WHEN game.winner IS NOT NULL THEN 
            IF(game.winner = home_t.id, home_t.name, away_t.name) 
        ELSE '未决出胜负' 
    END AS winner_name,
    home_conf.name AS home_conference_name,
    away_conf.name AS away_conference_name,
    game.week,
    game.confidence
FROM game
-- 关联主场球队
LEFT JOIN team home_t ON game.home_team = home_t.id
-- 关联客场球队
LEFT JOIN team away_t ON game.away_team = away_t.id
-- 关联主场球队所属会议
LEFT JOIN conference home_conf ON game.home_conference = home_conf.id
-- 关联客场球队所属会议
LEFT JOIN conference away_conf ON game.away_conference = away_conf.id;

语句说明 #

• 使用home_t、away_t作为team表的别名，分别对应home_team和away_team外键，避免字段冲突；
• 同理用home_conf、away_conf作为conference表的别名，获取主客场球队的会议名称；
• 通过CASE和IF函数处理winner字段，当有获胜球队时显示其名称，否则显示“未决出胜负”；
• 明确指定需要查询的字段，而不是用SELECT *，这样可以避免因多个关联表存在同名字段（比如id、name）导致的结果混乱，也让查询结果更清晰。

如果需要保留原始的ID字段（比如用于后续操作），可以在SELECT列表中添加，例如：game.home_team AS home_team_id。

内容的提问来源于stack exchange，提问作者Ezra Henley