SQL Database SELECT question

Need some help with an homework assignment on SQL

Problem

Find out who (first name and last name) has played the most games in the chess tournament with an ID = 41

Background information

I got a table called Games, which contains information...

• game ID
• tournament ID
• start_time
• end_time
• white_pieces_player_id
• black_pieces_player_id
• white_result
• black_result

...about all the separate chess games that have taken place in three different开发者_运维问答 tournaments ....

(tournaments having ID's of 41,42 and 47)

...and the first and last names of the players are stored in a table called People....

• person ID (same ID which comes up in the table 'Games' as white_pieces_player_id and black_pieces_player_id)
• first_name
• last_name

...how to make a SELECT statement in SQL that would give me the answer?

sounds like you need to limit by tournamentID in your where clause, join with the people table on white_pieces_player_id and black_pieces_player_id, and use the max function on the count of white_result = win union black_result = win.

interesting problem. what do you have so far?

hmm... responding to your comment

SELECT isik.eesnimi 
FROM partii JOIN isik ON partii.valge=isik.id 
WHERE turniir='41' 
group by isik.eesnimi 
having count(*)>4

consider using the max() function instead of the having count(*)> number

you can add the last name to the select clause if you also add it to the group by clause

sry, I only speak American. What language is this code in?

I would aggregate a join to that table to a derived table like this:

SELECT a.last_name, a.first_name, CNT(b.gamecount) totalcount 
FROM players a
JOIN (select cnt(*) gamecount, a.playerid
      FROM games  
      WHERE a.tournamentid = 47
      AND (white_player_id = a.playerid OR black_player_id = a.playerid)
      GROUP BY playerid
      ) b 
ON b.playerid = a.playerid
GROUP BY last_name, first_name
ORDER BY totalcount

something like this so that you are getting both counts for their black/white play and then joining and aggregating on that.

Then, if you only want the top one, just select the TOP 1