SQL query for OneToMany + ManyToMany tables

Question

I have 4 tables.

USER

id	email
1	b@mail.com
2	a@mail.com

TEAM

id	title	club
1	u7	1
2	U8	1

TEAM_USER

team_id	user_id
1	1
2	1

CLUB

id	title
1	manchester
2	madrid

I want to write a query which returns all users showing only their email (they have other columns as well) and the teams related to this user(in an array if possible) and a specific club. I want one row per user. I want all columns for the teams. I want to get back users which do not have related teams as well.

Is that possible?

So far this is my code:

SELECT u.email, u.first_name, u.last_name, GROUP_CONCAT(team.title)
FROM user u
LEFT JOIN team_user
    ON team_user.user_id = u.id
LEFT OUTER JOIN team
    ON team.club_id = u.club_id and team_user.team_id = team.id
GROUP BY email, first_name, last_name

But this returns only the team title in a comma-separated manner. Can I get back the other fields and in an array or object?

Answer 1

One option uses JSON ; we can generate an array of team objects for each user like so:

select u.*,
    (
        select json_arrayagg(json_object( 'id', t.id, 'title', t.title ) order by t.id )
        from teams t
        inner join team_user tu on tu.team_id = t.id
        where tu.user_id = u.id
    ) as json_teams
from users u 

Note that this still requires you to list all columns in the teams table, since MySQL does not provide a native function to turn a record to json object (unlike other databases such as Postgres).

I don’t see how table club comes into the picture, since it does not seem to relate to other tables.