按外键计数行

Question

I need a little help some a sql queries. To summarise, I have 2 tables. Player (which represents a sports player) and a Goal (which represents a goal a player scores). A Player can have many Goals and linked using a foreign key on the goal table (player_id).

What I want to do is get a list of "top scoring players" (top 5), but I have no idea where to start to do this using MySQL. In PHP I'm getting all the goals, then with each goal counting how many player_id's appear and group them like that (then with the array of players and their goal count, trimming the array down to 5). It works, but I'm almost positive I can do the counting in MySQL.

How should I approach this?

EDIT

Tables look like

Player

ID
Name

Goal

player_id
scored_against
time

Answer 1

SELECT COUNT(PLAYER.PLAYER_ID) as Goals,PLAYER_NAME 
FROM PLAYER, GOAL 
WHERE GOAL.PLAYER_ID = PLAYER.PLAYER_ID
GROUP BY GOAL.PLAYER_ID
ORDER BY Goals DESC
LIMIT 5