cs3mw
11-06-2009, 09:54 AM
Hi I have the following query:
SELECT g.playerID as playerID, SUM(g.goals) AS goalsscored, p.name, i.imgsource FROM goalscorer g, player p, regmatch r, playerimages i WHERE p.playerID=g.playerID AND r.matchID=g.matchID AND p.playerID=i.playerID AND i.mainimg='Yes' AND r.season='14' GROUP BY playerID ORDER BY goalsscored DESC
Which basically displays in descending order the players who have scored goals in a particular season. I would then like to display the number of LEAGUE goals and CUP goals that those players have scored. To do this I extract the playerID from the above statement and run the following SQL statements.
For league Goals
SELECT SUM(g.goals) AS goalsscored FROM goalscorer g, regmatch r WHERE r.matchID=g.matchID AND r.season='14' AND r.competition='3' AND g.playerID='$playerID' GROUP BY playerID ORDER BY goalsscored DESC
For Cup Goals
SELECT SUM(g.goals) AS goalsscored FROM goalscorer g, regmatch r WHERE r.matchID=g.matchID AND r.season='14' AND (r.competition='1' OR r.competition='2' OR r.competition='5') AND g.playerID='$playerID' GROUP BY playerID ORDER BY goalsscored DESC
Now this works fine but is there any way I incorporate the 3 sql statements into one so that the top statement lists in descending order the player who has scored the most goals, as well as calculating those that where LEAGUE goals and those that where CUP goals. Hope this make sense. I have made an attempt at this and simply get a subquery returns more than 1 row.
Kind Regards
SELECT g.playerID as playerID, SUM(g.goals) AS goalsscored, p.name, i.imgsource FROM goalscorer g, player p, regmatch r, playerimages i WHERE p.playerID=g.playerID AND r.matchID=g.matchID AND p.playerID=i.playerID AND i.mainimg='Yes' AND r.season='14' GROUP BY playerID ORDER BY goalsscored DESC
Which basically displays in descending order the players who have scored goals in a particular season. I would then like to display the number of LEAGUE goals and CUP goals that those players have scored. To do this I extract the playerID from the above statement and run the following SQL statements.
For league Goals
SELECT SUM(g.goals) AS goalsscored FROM goalscorer g, regmatch r WHERE r.matchID=g.matchID AND r.season='14' AND r.competition='3' AND g.playerID='$playerID' GROUP BY playerID ORDER BY goalsscored DESC
For Cup Goals
SELECT SUM(g.goals) AS goalsscored FROM goalscorer g, regmatch r WHERE r.matchID=g.matchID AND r.season='14' AND (r.competition='1' OR r.competition='2' OR r.competition='5') AND g.playerID='$playerID' GROUP BY playerID ORDER BY goalsscored DESC
Now this works fine but is there any way I incorporate the 3 sql statements into one so that the top statement lists in descending order the player who has scored the most goals, as well as calculating those that where LEAGUE goals and those that where CUP goals. Hope this make sense. I have made an attempt at this and simply get a subquery returns more than 1 row.
Kind Regards