Subquery inside INNER JOIN (MySQL Workbench)

Question

I'm working on a relational database with information for video games and their users. I want to create a view that displays the first name, last name and email of users that play a particular game (e.g. FIFA 22).

There are three tables in my database:

1. The table "NAME" contains the id (id_game), the name (name) and description (description) for each game.

2. The table "PLAY" contains the id (id_game) for each game and the id (id_system_user) for each user.

3. The table "SYSTEM_USER" contains the id (id_system_user), the first name (first_name), the last name (last_name) and the email (email) for each user

I've tried creating the view and then using a subquery inside an INNER JOIN to join the three tables but I get an error every time I execute it. The code is the following:

CREATE OR REPLACE VIEW vw_FIFA22 AS
    (SELECT S.first_name AS "First name",
            S.last_name AS "Last name",
            S.email AS "E-Mail"
     FROM SYSTEM_USER AS S
     INNER JOIN (SELECT G.name AS "Game name",
                        P.id_system_user AS "System user"
                 FROM GAME AS G
                 INNER JOIN PLAY AS P ON (G.id_game = P.id_game)
                 WHERE G.name = "FIFA 22") AS N
     ON (S.id_system_user = N.id_system_user)
    );

and the error is:

Error Code: 1054. Unknown column 'N.id_system_user' in 'on clause'

Then I tried running the next code and worked out:

CREATE OR REPLACE VIEW vw_FIFA22 AS
    (SELECT S.first_name,
            S.last_name,
            S.email
     FROM SYSTEM_USER AS S
     INNER JOIN PLAY AS P
     ON (S.id_system_user = P.id_system_user)
     WHERE id_game = (SELECT id_game
                      FROM GAME
                      WHERE name = "FIFA 22")
    );

Also, after reading the answers for this question (MySQL INNER JOIN with SubQuery (well detailed)), I tried running this other variation and it also worked:

CREATE OR REPLACE VIEW vw_FIFA22 AS
    (SELECT S.first_name AS "First name",
            S.last_name AS "Last name",
            S.email AS "E-Mail"
     FROM (SELECT P.id_system_user
           FROM GAME AS G
           INNER JOIN PLAY AS P ON (G.id_game = P.id_game)
           WHERE G.name = "FIFA 22") AS P
     INNER JOIN SYSTEM_USER AS S
     ON S.id_system_user = P.id_system_user
    );

Why did these two variations work out well but the first code didn't?

Answer 1

You just need to join the 3 tables together, there is no need for subqueries e.g.

CREATE OR REPLACE VIEW vw_FIFA22 AS
(SELECT S.first_name AS "First name",
        S.last_name AS "Last name",
        S.email AS "E-Mail"
 FROM SYSTEM_USER AS S
 INNER JOIN PLAY AS P ON S.id_system_user = P.id_system_user
 INNER JOIN GAME G ON G.id_game = P.id_game
 WHERE G.name = "FIFA 22"
 ;