MySQL Join Over 5 tables

The Situation

We have these 5 different MySQL tables:

Table club

Id | ClubName | CityId
---+----------+--------
1  | Test     | 1

Table city

Id | CityName | CountryId
---+----------+-----------
1  | London   | 1

Table country

Id | CountryName
---+------------
1  | UK

Table Genre

Id | GenreName
---+-----------
1  | Rock
2  | Classic
3  | Techno

Table club2genre

ClubId | GenreId
-------+--------
1      | 1
1      | 2
1      | 3

What is expected

The expected result would be this:

ClubId | Club | City   | Country | Music
-------+------+--------+---------+---------------------
1      | Test | London | UK      | Rock,Classic,Techno

What I’ve tried

Using the following query

SELECT
    club.Id AS ClubId,
    club.ClubName AS Club,
    GROUP_CONCAT(genre.GenreName) as Music
FROM
    club
INNER JOIN club2genre
    ON club2genre.clubid = club.id
INNER JOIN genre
    ON genre.id = club2genre.genreid
GROUP BY 
    club.Id;

I managed to get the following result:

ClubId | Club | Music
-------+------+-------------------
1      | Test | Rock,Classic,Techno

However I’ve now been trying for hours to somehow link the City and Country to the result but stuff like this:

SELECT
    club.Id,
    club.ClubName AS Name,
    city.CityName AS City,
    country.CountryName AS Country,
    CONCAT(genre.GenreName) as Music
FROM
    club, city, country
INNER JOIN club2genre
    ON club2genre.clubid = club.id
INNER JOIN genre
    ON genre.id = club2genre.genreid
WHERE
    club.CityId = city.Id AND city.CountryId = country.Id;

doesn’t work and MySQL just throws errors like ERROR: 1054 (42S22): Unknown column 'club.id' in 'on clause'. Then again it this point I’m more guessing than actually knowing what I’m doing. I assume I’d have to throw even more joins in there? I’m not that familiar with MySQL and it’s been ages since I touched SQL in general so any help would be greatly appreciated.

Answer

You are mixing explicit and implicit join syntax.Use explicit only.

Also you should properly GROUP BY all columns that are in SELECT clause without aggregate function

That gives:

SELECT
    club.Id,
    club.ClubName AS Name,
    city.CityName AS City,
    country.CountryName AS Country,
    CONCAT(genre.GenreName) as Music
FROM
    club
INNER JOIN city
    ON club.CityId= city.id
INNER JOIN country
    ON country.Id= city.CountryId
INNER JOIN club2genre
    ON club2genre.clubid = club.id
INNER JOIN genre
    ON genre.id = club2genre.genreid
GROUP BY 
    club.Id, club.ClubName, city.CityName, country.CountryName;