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.

Show us your tables please - with some sample data ofcourse and expected output. — bonCodigo, Dec 07 '12 at 21:32
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (**20 years** ago!). ***Please*** stop using it — marc_s, Dec 07 '12 at 21:32
Shouldn't `1.p2sStatistic` and `3.p2sStatistic` be `p2sStatistic.1` and `p2sStatistic.3` instead? — Michael Fredrickson, Dec 07 '12 at 21:33
Start by disclosing the database system you use and its version number. — Erwin Brandstetter, Dec 07 '12 at 22:00
Please don't put important info in comments, **edit** your question. -> Link left under your question. — Erwin Brandstetter, Dec 07 '12 at 22:02
I edited the question to fix its logic how I understand it. Please edit if I didn't get you right. And add your **database system and version number**. This should *always* be included! — Erwin Brandstetter, Dec 07 '12 at 22:34

score 0 · Answer 1 · answered Dec 07 '12 at 22:06

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)

score 0 · Answer 2 · answered Dec 07 '12 at 22:09

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;

score 0 · Accepted Answer · edited May 23 '17 at 10:34

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

Query different IDs with different values?

3 Answers3