I am having trouble getting the format correct for a subquery.
One of my queries displays the following:
This involves making calls to three tables (Ind Stats table, that pulls Game Stats, and Teams Name). As you can see, this query works fine.
What I'm having troubles with is how to pull each player that had a DD in that particular game. Players is another able within the database. All I can understand is that I should need a sub-query but sub-queries are not my strong suit.
Here is my code
SELECT * FROM
(SELECT game_Date,
game_GameID,
game_SeasonID,
game_TeamCode,
teams_Name,
stats1_GameID,
Sum(stats1_DD) AS 'TotGameDD'
FROM game INNER JOIN teams INNER JOIN stats1
ON
teams_TeamCode = game_TeamCode
AND
game_GameID = stats1_GameID
GROUP BY game_GameID
HAVING Sum(stats1_DD) >1
ORDER BY Sum(stats1_DD) desc) t
INNER JOIN players INNER JOIN stats1 INNER JOIN game
ON stats1_GameID = game_GameID
AND
sstorm_rawstats1.stats1_PlayerID = sstorm_players.player_PlayerID
"
So, to be clear, this works fine:
SELECT game_Date,
game_GameID,
game_SeasonID,
game_TeamCode,
teams_Name,
stats1_GameID,
Sum(stats1_DD) AS 'TotGameDD'
FROM game INNER JOIN teams INNER JOIN stats1
ON
teams_TeamCode = game_TeamCode
AND
game_GameID = stats1_GameID
GROUP BY game_GameID
HAVING Sum(stats1_DD) >1
ORDER BY Sum(stats1_DD) desc
I'm just trying to display the name of the players who recorded a DD in the row here. I'm getting the name now but it is counting all games that the player played. I'm close now.
UPDATE: I can now display the results in the table but it only shows one player who got a DD. I'm thinking I need a loop to display all the users who have a DD. Any help, much appreciated.
I just changed my above code by adding a GROUP BY stats1_DD and another AND in the FROM Statement as such:
FROM game INNER JOIN teams INNER JOIN stats1
ON
teams_TeamCode = game_TeamCode
AND
game_GameID = stats1_GameID
AND
game_PlayerID = player_PlayerID
GROUP BY game_GameID, stats1_DD
HAVING Sum(stats1_DD) >1
ORDER BY Sum(stats1_DD) desc