How can I do this in a single query for all the games, there can be many types of games. I cant specify the games explicitly, instead, I want to write the query to fetch all the playerids who are not playing a game for each of the game some thing like this
the above query is giving only one record. I need like this
Yes, this can be done in raw SQL. It's not particular efficient; doing a loop behind the scenes. But for small datasets, that is fine.
The nested query gets the games a player played. If there aren't any for that player/match combo, the outer query includes the data. The distinct is because the cartesian product of the outer query creates duplicate rows.