Need help on SQL query

Hi Guys

I’m a newbie in databases and SQL worl. I have a problem. I’ve spent 12 hours but couldn’t find the solution.

Can anyone give me a sample code, or show me the right direction please?

Here is the scenario:

I will need to find out the number of goals a player has scored in year 2009, and display various information about him such as name, shirt no etc.

The only part I couldn’t figure out is the bit where I have to add all the goals scored by a certain player, and display his name, position, total number of goals scored in 2009, and shirt no.

I will be grateful if anyone can help me on this, otherwise I’m going to go mad.







sorry, we won’t do your homework for you

show us what you’ve tried and what kind of problems you’re having

Hi “r37”

Sorry for not including the code in the first place.

This is what i’ve done so far:

SELECT, teams.teamName, performance.shirtNo, players.position, performance.goals FROM players, performance, teams WHERE goals = (SELECT MAX(goals) FROM performance) AND players.teamID = teams.teamID AND players.teamID = performance.teamID;

E.g:Which results in:

name - teamName - shirtNo - position - goals
Henry - Barcelona - 9 - FW - 9

This information is based on the maximum goal in the performance table. But I need to add all the goals scored by each player individualy and display the above information based on the highest goal (E.g. total goals scored by the player with the teamID=2).

I’ve used “COUNT(),” “SUM() GROUP BY teamID” statement, I can count the number of goals scored by each player, but I can’t combine the two code. (E.g Count the goals for each player, display the above infromation based on the highest goal) I’ve looked into JOIN, subqueries and I just couldn’t figure it out.

Hope this helps.

sorry, i don’t understand what this means

Lets say we’ve got a “playedMatches” table. This table stores each player’s performance based on number of goals scored, minutes played etc with a unique teamID and playedMatchesID. If a player has played in 3 matches, the table will store player’s performance for each of those 3 matches. What I want to do is, add the goals for each player, and display information from 3 different tables based on that player (or who ever has scored the highest goals).

Is this even possible in mysql?

To be more precise, I want to sum all the goals in the performance table, then group by to see who has scored the highest goals, and display that players name shirt number, position etc. ( which is held in 3 different tables )

In the above example, I can display a players information who has scored the highest goals in a single game, but i need to display the player who has scored the most goals in all games.

Does that make sense? If not I’ll try explaining it again :smiley:

and how is that explanation not satisfied by your query in which you have

WHERE goals = (SELECT MAX(goals) FROM performance) 

But I need to add the goals before finding the MAX goals, and thats what I can’t seem to figure out. I need to add the goals for each player, then find the highest. I’ve uploaded a screenshot, the player with the teamID = 2 has played two games, in the first game he has scored 4 goals, in the second game he has scored 5 goals, now I need to add these two games (for each player) then find the highest.

Sorry to be a hadache :frowning:

show me the query which computes the total goals for each player

select name,shirtno,position,SUM(goals) from players p LEFT JOIN playerPerformance pp on pp.shirtNo = p.shirtNo LEFT JOIN playedMatches pm on pm.matchID=pp.matchID group by shirtNo

try this one this will give you what u want

     ,SUM(IF(YEAR( = 2009,pp.goals,0)) goals_09
     ,SUM(pp.goals) lifetime_goals
     players p
     playerPerformance pp
     p.shirtNo = pp.shirtNo
     playerMatches pm
     pp.matchID = pm.matchID

i think sircez’s assignment was due today…


:slight_smile: no it’s next week, I’ve actually done it, but it was pure guess work and help from you guys, and trying out different statements for the last 7 days.

I’m trying to understand the code now, but I still don’t get LEFT JOIN, subqueries, nested queries etc. So I’m looking those up, but the assignment is done never the less, will hand it in next week, thank you guys :slight_smile:

Can you suggest me some tutorials or books on this stuff? I want to be comfortable with SQL, also there’s an exam soon :shifty:

Many thanks again to all of you.

hmm… a book about SQL… lemme think for a sec…

Found this , don’t know if it’s any good but i might try. I want one exclusively for MYSQL, still looking…

Maybe r937 knows if it’s any good? :smiley:

oh yeah, it’s awesome

:smiley: :smiley: :smiley:

You can’t trust his opinion because its bias. :rofl:

I’m a disciple of the book author :smiley: