Use STRING_AGG to get Values from 3 different Tables

I am trying to get comma-separated values from the following 3 tables:

Teams: Id

Players: Id, Name

TeamsPlayers: Id, TeamId, PlayerId

What I am trying to get is a result like this:

sql

SELECT STRING_AGG(Name, ',')
FROM (
    SELECT tp.TeamId, p.Name
    FROM Players p
    JOIN TeamPlayers tp ON p.Id = tp.PlayerId
    GROUP BY tp.TeamId, p.Name
    )

Expanding on Larnu's comment.

Asssuming your initial query has the proper elements

SELECT tp.TeamId, 
      ,Names = STRING_AGG(p.Name, ',')
 FROM  Players p
 JOIN TeamPlayers tp ON p.Id = tp.PlayerId
 GROUP BY tp.TeamId