Selecting all rows related rows when row contains user-CodePudding

Let's say I have the following simple layout (this is a simplified version of what I have, in reality I'm using TypeORM to achieve this);

user(id, name)
game(id, name)
players(id, userId, gameId)

Now I would like to retrieve all games and players in which the user participated.

The following query would obtain all players in a game;

SELECT g.id, p.* FROM games g JOIN players p ON g.id = p.gameId

Now I however want to filter this by only selection all the games and players a specific user has participated in. What I did was the following;

SELECT g.id, p.* FROM games g JOIN players p ON g.id = p.gameId WHERE p.userId = :userId

Now this obviously returns all the rows of the player. But how could I also get the other players in which :userId participated? It seems like such an easy thing to do but I can't really wrap my head around it.

CodePudding user response：

Try this query:

SELECT g.id, p.* 
FROM games g JOIN players p ON g.id = p.gameId 
WHERE g.id IN (
  SELECT u.gameId
  FROM players u
  WHERE u.userId = :userId
)

CodePudding user response：

You can try joining players table twice and games table once:

SET @userid := 1;

SELECT g.id, p1.*
FROM players p1
 JOIN players p2
   ON p1.gameId=p2.gameId
 JOIN games g 
   ON p1.gameId=g.id
WHERE p1.userId= @userId;

Demo fiddle