Query help with multiple Left joins

I am trying to write a query which will allow me to display a list of players in a football team, looking up the number of goals (from a seperate table) and the number or appearences (from a seperate table).

The query I have is as follows: -

SELECT name, COUNT( goals.playerId ), COUNT( playing.playerId)
FROM player
LEFT JOIN goals 
ON player.id = goals.playerId
LEFT JOIN playing
ON player.id = playing.playerId
GROUP BY goals.playerId, playing.playerId, player.id

Code (markup):

This query almost works, except for when a playerId is present in goals AND playing it seems to add them together in both columns rather than counting the correct number for each column.

I want to get this

Player Name | No Of Goals | No of Apperences-
---------------------------------------------------
John Smith | 4 | 7
Jack Black | 2 | 9
Micheal Ball | 1 | 6
Jim Cricket | 0 | 6

Where a I am currently getting something like this:

Player Name | No Of Goals | No of Apperences-
---------------------------------------------------
John Smith | 11 | 11
Jack Black | 11 | 11
Micheal Ball | 7 | 7
Jim Cricket | 0 | 6


Any idea what my problem is? How I am grouping it? a different way of doing it?

In the long run I want to be able to call this query but using a variable ORDER BY clause so I can order the players by apperences and goals scored, not just name.

Thanks

 

I'm pretty sure you are going to need 2 sub-queries. It depends on how the goals and playing tables are structured.

Can a playerId appear in multiple records in those 2 tables? If so, then you must first group up each of those tables individually by playerID along with your Count field. Then you Left Loin those two queries to the player table.