Question

Can someone please help me. I am having trouble writing the following SQL query: "Retrieve the name and country of the player with the most yellow cards in the 2014 world cup." The player and country are in the player table, and the yellow cards are in the player_cards table. The tables can be connected on player_id. The problem I am having is that there are 3 players that have 3 yellow cards, not just a single player. (3 yellow cards is the most that any player has in my tables.) Thank you for any help you can give.

edited to add: I can get the query to work if I set the where player_cards = 3, but I need to do it using MAX somehow.

Answer 1

SQL query: "Retrieve the name and country of the player with the most yellow cards in the 2014 world cup."

Player Table (player_id, player_name, country)

Player_cards Table (player_id, yellow_cards)

SELECT
p.player_name, p.country
FROM
player p INNER JOIN player_cards c
ON p.player_id = c.player_id
WHERE c.yellow_cards = (
       SELECT DISTINCT MAX(c1.yellow_cards) FROM
       player p1 INNER JOIN player_cards c1
       ON p1.player_id = c1.player_id
       GROUP BY p1.player_id
   )

*** To select for the game 2014 world cup, you can put condition as game = "2014 world cup" in WHERE clause.

*** Sub-query is used to find the MAX(yellow_cards) from the table and use it in the WHERE clause.