Query different IDs with different values?

Question

I'm trying to write a query for a golf database. It needs to return players who have statisticID = 1 with a p2sStatistic > 65 and who also have statisticID = 3 with p2sStatistic > 295.
One statisticID is driving distance, the other accuracy, etc. I've tried the following but it doesn't work and can't seem to find an answer online. How would I go about this without doing a view?

SELECT playerFirstName, playerLastName
FROM player2Statistic, player 
WHERE player.playerID=player2Statistic.playerID
AND player2Statistic.statisticID=statistic.statisticID
AND p2sStatistic.3 > 295
AND p2sStatistic.1 > 65; 

https://i.stack.imgur.com/fR5OL.png - pic of db

Trying to get it just output the list of players that satisfy those two conditions.

Answer 1

For a list of players without duplicates an EXISTS semi-join is probably best:

SELECT playerFirstName, playerLastName
FROM   player AS p 
WHERE EXISTS (
   SELECT 1
   FROM   player2Statistic AS ps 
   WHERE  ps.playerID = p.playerID
   AND    ps.StatisticID = 1
   AND    ps.p2sStatistic > 65
   )
AND EXISTS (
   SELECT 1
   FROM   player2Statistic AS ps 
   WHERE  ps.playerID = p.playerID
   AND    ps.StatisticID = 3
   AND    ps.p2sStatistic > 295
   );

Column names and context are derived from the provided screenshots. The query in the question does not quite cover it.
Note the parenthesis, they are needed to cope with operator precedence.

This is probably faster (duplicates are probably not possible):

SELECT p.playerFirstName, p.playerLastName
FROM   player           AS p 
JOIN   player2Statistic AS ps1 USING (playerID)
JOIN   player2Statistic AS ps3 USING (playerID)
AND    ps1.StatisticID = 1
AND    ps1.p2sStatistic > 65
AND    ps3.StatisticID = 3
AND    ps3.p2sStatistic > 295;

If your top-secret brand of RDBMS does not support the SQL-standard (USING (playerID), substitute: ON ps1.playerID = p.playerID to the same effect.

It's a case of relational division. Find many more query techniques to deal with it under this related question:
How to filter SQL results in a has-many-through relation

Answer 2

You are missing the statistic table in your query. You need to join it in, based on your where clause.

You also need to use proper join syntax.

The following version joins in the statistics table twice, once for the "1" and once for the "3":

SELECT distinct playerFirstName, playerLastName
FROM player2Statistic p2s join
     player p
     on p.playerId = p2s.playerId join
     statistic s3
     on s3.StatisticId = p2s.statistcId and
        s3.StatisticId = 3 join
     statistic s1
     on s1.StatisticId = p2s.statisticId and
        s1.StatisticId = 1
WHERE  (s3.statistic > 295 and s1.statistic > 65)

Answer 3

You will want to join to the statistics table twice:

SELECT playerFirstName, playerLastName
  FROM player p
  JOIN player2Statistic s1
    on p.playerID=s1.playerID and s1.statisticID = 1
  JOIN player2Statistic s3
    on p.playerID=s3.playerID and s1.statisticID = 3
 WHERE s1.p2sStatistic > 65 and s3.p2sStatistic > 295;