SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    players
    --------
    shirtNo
    name
    position


    playedMatches
    ---------------
    matchID
    date


    playerPerformance
    ------------------
    goals
    minutes
    booked
    matchID
    shirtNo

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, we won't do your homework for you

    show us what you've tried and what kind of problems you're having
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi "r37"

    Sorry for not including the code in the first place.

    This is what i've done so far:

    SELECT players.name, 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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SirCez View Post
    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).
    sorry, i don't understand what this means
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    and how is that explanation not satisfied by your query in which you have
    Code:
    WHERE goals = (SELECT MAX(goals) FROM performance)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and how is that explanation not satisfied by your query in which you have
    Code:
    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
    Attached Images Attached Images

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    show me the query which computes the total goals for each player
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         p.name
         ,p.shirtNo
         ,p.POSITION
         ,SUM(IF(YEAR(pm.DATE) = 2009,pp.goals,0)) goals_09
         ,SUM(pp.goals) lifetime_goals
      FROM
         players p
      LEFT OUTER
      JOIN
         playerPerformance pp
        ON
         p.shirtNo = pp.shirtNo
      LEFT OUTER
      JOIN
         playerMatches pm
        ON
         pp.matchID = pm.matchID
     GROUP
        BY
         p.shirtNo

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i think sircez's assignment was due today...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i think sircez's assignment was due today...

    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

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

    Many thanks again to all of you.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SirCez View Post
    Can you suggest me some tutorials or books on this stuff?
    hmm... a book about SQL... lemme think for a sec...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Found this http://www.sitepoint.com/books/sql1/ , don't know if it's any good but i might try. I want one exclusively for MYSQL, still looking...

  15. #15
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by SirCez View Post
    ...don't know if it's any good...
    Maybe r937 knows if it's any good?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    oh yeah, it's awesome

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You can't trust his opinion because its bias.

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm a disciple of the book author

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •