Apply different independent filters to the same column in a GROUP BY

Question

I have 2 tables, players and stats.

Table players have an id and a name, while the table stats looks like this:

id  | Player    | Stat           | Value
1   | 0000001   | Wins           |     5
2   | 0000001   | Loses          |     6
3   | 0000001   | Jumps          |   156
4   | 0000001   | Shots          |   580
5   | 0000002   | Wins           |    15
6   | 0000002   | Loses          |     2
7   | 0000002   | Jumps          |   530
8   | 0000002   | Shots          |  1704

I want to filter players that match several conditions, like, for example, players that have more than 5 wins but less than 200 jumps.

I tried this

SELECT players.name
FROM players
LEFT JOIN stats
  ON stats.player = players.id
WHERE (stats.stat = "Wins" AND stats.value > 5)
  AND (stats.stat = "Jumps" AND stats.value < 200)
GROUP BY players.id

But it returns nothing, because the GROUP BY goes after the WHERE. I also tried using OR.

SELECT players.name
FROM players
LEFT JOIN stats
  ON stats.player = players.id
WHERE (stats.stat = "Wins" AND stats.value > 5)
   OR (stats.stat = "Jumps" AND stats.value < 200)
GROUP BY players.id

But in that case, it returns the players that match any of the conditions, and I only want the ones that match both conditions. In this specific example, it should only return the player with id 0000001.

I know I could do it with a different LEFT JOIN for every different stat, but truth is the actual table is huge and has tons of diferent stats, so I don't think that is an option because it would be very slow.

Answer 1

There is no need to aggregate. You can do this with two inner joins, one per condition:

SELECT p.name
FROM player p
INNER JOIN stats s1 ON s1.player = p.id AND s1.stat = 'Wins' AND s1.value > 5
INNER JOIN stats s2 ON s2.player = p.id AND s2.stat = 'Jumps' AND s2.value < 200

With an index on stats(player, stat, value), this should be an efficient option.