Create MySQL Select Query for 2 Table with 1 Foreign Key in 2 Column in one Table

Question

I have 2 table:

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

How I can create MySQL SELECT query for the result like this:

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

I had try this:

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

But there is no result

I also tried this:

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

But I get : #1242 - Subquery returns more than 1 row.

Answer 1

you need to join the club in the query twice. use different aliases.

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