below are the queries i need to execute to get the required information that i need. At present i plan to excute each query and pass the results on to the next query to try a get the correct output. (i'm not great at sql) i'm using php. I would like to know if there's an easier why to approach this and if so how.
Code: Select all
Get games a team played this season:
SELECT r.iPoints AS 'Points', f.sName AS 'Fixture', f.fixtureID AS
'FixtureID', t1.sName AS 'Team_Home', t2.sName AS 'Team_Away',
r.iGoalsConceded AS 'GoalsAgainst' FROM
fixture f INNER JOIN result r ON (f.fixtureID = r.iFixtureID) INNER JOIN
team t1 ON (f.iHomeTeamID = t1.TeamID) INNER JOIN team t2 ON (f.iAwayTeamID
= t2.TeamID)
WHERE f.iSeasonID = 1 AND f.iDivisionID = 1 AND r.iTeamID = 1 AND
(f.iHomeTeamID = 1 OR f.iAwayTeamID = 1)
Get list of team players who played in games that team won:
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'GamesWon',
COUNT(ps.iFixtureID) * 3 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2)
GROUP BY p.sName
Get list of team players who played in games that team drew:
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'GamesDrawn',
COUNT(ps.iFixtureID) * 1 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2)
GROUP BY p.sName
Get list of team players who scored goals in games this season:
SELECT p.playerID, p.sName, SUM(ps.iGoalsScored) AS 'GoalsScored',
SUM(ps.iGoalsScored) * 3 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iGoalsScored <> 0
GROUP BY p.sName
Get list of team players who registered goal assists in games this season:
SELECT p.playerID, p.sName, SUM(ps.iGoalAssist) AS 'GoalAssists',
SUM(ps.iGoalAssist) * 2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iGoalAssist <> 0
GROUP BY p.sName
Get list of team players who got man of the match in games this season:
SELECT p.playerID, p.sName, SUM(ps.bManOfMatch) AS 'ManofMatch',
SUM(ps.bManOfMatch) * 5 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.bManOfMatch <> 0
GROUP BY p.sName
Get list of team players who got yellow cards in games this season:
SELECT p.playerID, p.sName, SUM(ps.iYellowCard) AS 'YellowCards',
SUM(ps.iYellowCard) * -2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iYellowCard <> 0
GROUP BY p.sName
Get list of team players who got red cards in games this season:
SELECT p.playerID, p.sName, SUM(ps.bRedCard) AS 'RedCards', SUM(ps.bRedCard)
* -2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.bRedCard <> 0
GROUP BY p.sName
Get list of fixtures this season that team kept a clean sheet:
CREATE TEMPORARY TABLE cleansheet SELECT f.fixtureID AS 'fixtureID' FROM
fixture f INNER JOIN result r ON (f.fixtureID = r.iFixtureID)
WHERE f.iSeasonID = 1 AND f.iDivisionID = 1 AND r.iTeamID = 1 AND
r.iGoalsConceded = 0 AND (f.iHomeTeamID = 1 OR f.iAwayTeamID = 1)
Get list of team goalkeepers who kept cleansheets this season
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'KeeperCleansheets',
COUNT(ps.iFixtureID) * 4 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1) AND ps.iPositionID = 1
GROUP BY p.sName
Get list of team defenders who kept cleansheets this season
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'DefenderCleansheets',
COUNT(ps.iFixtureID) * 2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1) AND ps.iPositionID = 2
GROUP BY p.sNamethanks sinus