Basic SQL query

Question

Shown below is the SQL database and query I am a bit stuck on creating. I'm just unsure on how to specify that the results needs to show people who have played in more than two matches.

This is my query so far:

SELECT playerno
FROM PLAYERS, MATCHES
WHERE PLAYERS.playerno = MATCHES.playerno
AND town = ‘Manchester’;

You are given the following tennis database.

PLAYERS (playerno, name, leagueno, year_of_birth, town, street, year_joined, postcode)
TEAMS (teamno, playerno, division)
MATCHES ( matchno, teamno, playerno, sets_won, sets_lost)
PENALTIES (paymentno, playerno, pendate, amount)

Q) Find the player number of players who appear in more than two matches and live in ‘Manchester’

So which version of sql are you using? You should only tag the correct one. — Andrew, Jan 09 '16 at 01:09
Possible duplicate of [Select where count of one field is greater than one](http://stackoverflow.com/questions/3710483/select-where-count-of-one-field-is-greater-than-one) — Andrew, Jan 09 '16 at 01:10
[This](http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1) question seems to address the same thing. — wesanyer, Jan 09 '16 at 01:10
What @Andrew is referring to is that you tagged both mysql and sql-server (totally different servers) and that could have effect on the answers! — davejal, Jan 09 '16 at 01:17
@davejal Okay, will bare this in mind next time I ask a question — Jay, Jan 09 '16 at 01:28

score 0 · Answer 1 · answered Jan 09 '16 at 01:12

0

Use 'group by' and 'having'.

e.g,

SELECT 
    playerno
FROM 
    PLAYERS,
    MATCHES
WHERE 
    PLAYERS.playerno = MATCHES.playerno
    AND town = ‘Manchester’
GROUP BY
    playerno
HAVING
    COUNT(*) >= 2;

answered Jan 09 '16 at 01:12

Dylan James McGannon

834
1
7
14

spencer7593 · Accepted Answer · 2016-01-09T01:39:33.377

There's a couple of approaches you can use.

One is to make use of an aggregate function and a GROUP BY clause to "collapse" rows with identical values. We'll assume that we'd determine that a player was in more than two matches if there are at least three rows in the matches table for that player.

(Also, ditch the old-school comma syntax for the join operation, and use the JOIN keyword instead.)

Current query:

SELECT p.playerno
  FROM PLAYERS p
  JOIN MATCHES m 
    ON m.playerno = p.playerno
 WHERE p.town = 'Manchester'

Adding a GROUP BY clause to collapse rows that have the same value for playerno.

SELECT p.playerno
     , COUNT(1)
  FROM PLAYERS p
  JOIN MATCHES m 
    ON m.playerno = p.playerno
 WHERE p.town = 'Manchester'
 GROUP BY p.playerno
 ORDER BY p.playerno

The ORDER BY isn't strictly necessary, but it makes the returned result more deterministic. (Absent an ORDER BY clause, MySQL is free to return the rows in whatever order it chooses.)

The next trick is to reference the result of the aggregate function (COUNT(1)) in a HAVING clause. This can't go into the WHERE clause, because the predicates in the WHERE clause get evaluated as the rows are accessed. We want to apply a predicate after the rows have been accessed, and the result of the aggregate has been determined.

SELECT p.playerno
  FROM PLAYERS p
  JOIN MATCHES m 
    ON m.playerno = p.playerno
 WHERE p.town = 'Manchester'
 GROUP BY p.playerno 
HAVING COUNT(1) > 2
ORDER BY p.playerno

This assumes that the value of matchno isn't duplicated for the same player. That is, there won't be any two rows with the same values of the tuple (playerno,matchno).

If we didn't have that guarantee, but we were guaranteed that matchno was non-null (at least on the rows we want to consider)... to get a count of the unique values of matchno, we could replace the aggregate COUNT(1) with COUNT(DISTINCT m.matchno).

That's an example of just one approach. There are a couple others...

Another approach is to use a unique column (or set of columns), inequality comparisons, and multiple references to the matches table. And again assuming that we want to consider only unique value of matchno for a given player to be included in the count...

We can use a join operation, to restrict the rows returned to only those where there is another row in matches for the player that has a lower value of matchno.

SELECT p.playerno
  FROM players p
  JOIN matches m1  ON m1.playerno = p.playerno
  JOIN matches m2  ON m2.playerno = p.playerno AND m2.matchno > m1.matchno 
  JOIN matches m3  ON m3.playerno = p.playerno AND m3.matchno > m2.matchno 
 WHERE p.town = 'Manchester'    
GROUP BY p.playerno
ORDER BY p.playerno

Another approach is to use an EXISTS predicates, to perform a similar check. Does a there exist a row in matches for the player, where there exists another row in matches for the same player with greater value of matchno, ...

Something like this:

SELECT p.playerno
  FROM players p
 WHERE p.town = 'Manchester'
   AND EXISTS
       ( SELECT 1 
           FROM matches m1
          WHERE m1.playerno = p.playerno
            AND EXISTS 
                ( SELECT 1 
                    FROM matches m2
                   WHERE m2.player_no = m1.playerno
                     AND m2.matchno   > m1.matchno
                     AND EXISTS 
                         ( SELECT 1
                             FROM matches m3
                            WHERE m3.playerno = m2.playerno
                              AND m3.matchno  > m2.matchno
                         )
                ) 
       )
ORDER BY p.playerno

Basic SQL query

2 Answers2