mysql - 创建MySQL选择查询2个表，其中1个表的2列中有1个外键

我有2张桌子：

Table club:
id_club | club_name
   1    | PERSIB
   2    | PSMS
   3    | PSGC
PK(id_club)

Table match:
id_match |  matchday  | home | away
   1     | 2013-10-10 |   1  |  2
   2     | 2013-10-15 |   2  |  1
   3     | 2014-11-15 |   3  |  1
   4     | 2014-12-15 |   2  |  3
PK(id_match)
FK(home) REFERENCES club.id_club
FK(away) REFERENCES club.id_club

我如何为这样的结果创建MySQL SELECT查询：

Matchday   | Home | Away
2014-11-15 | PSGC | PERSIB
2014-12-15 | PSMS | PSGC

我尝试了这个：

SELECT m.matchday, c.club_name as home, c.club_name as away
FROM match m join club c on m.home=c.id_club && m.away=c.id_club
WHERE year(matchday)=2014

但是没有结果


我也尝试过这个：

SELECT
(SELECT matchday FROM match WHERE year(matchday)=2014) AS Matchday,
(SELECT c.club_name FROM club c JOIN match m ON m.home=c.id_club AND year(m.matchday)=2014) AS Home,
(SELECT c.club_name FROM club c JOIN match m ON m.away=c.id_club AND year(m.matchday)=2014) AS Away

但我得到：＃1242-子查询返回的行数超过1。

最佳答案

您需要在查询中加入俱乐部两次。使用不同的别名。

SELECT m.matchday, h.club_name as home, aw.club_name as away
FROM match m join club h on m.home=h.id_club
             join club aw on m.home=aw.id_club
WHERE year(matchday)=2014