Combined COUNTS

[NiallFH]

This feels like it's a complicated one, I'll try to explain it as best I can.

 

I have a table of players - tplss_players - fields PlayerID, PlayerName

I have a table of players who have made an appearance for their team = fields AppearanceID, AppearancePlayerID, AppearanceMatchID

I have a table of matches that have been played - MatchID, MatchDate

 

On the page I am working with, $matchdate is a variable already declared using a $_GET.

 

To clarify, PlayerID and AppearancePlayerID are related, as are MatchID and AppearanceMatchID.

 

I want to present one result which tells me the TOTAL amount of appearances made in the past by everyone who appears on that matchdate.

 

In other words, a team of 11 players played on 13th September 2013, I want to show the total of amount of games all eleven players have played prior to that date.  As a combined figure.

 

If anyone can offer some help, I'd be extremely grateful.

[Barand]

Find the players who played on that date (subquery) then match against those players with a join to the subquery

SELECT ap.playerID, COUNT(ap.matchID) as total
FROM appearances ap
    INNER JOIN
    (
    SELECT a.playerID
    FROM match m
        INNER JOIN appearances a ON m.matchID = a.appearanceMatchID
    WHERE m.matchDate = '$matchdate'
    ) selected USING (playerID)
GROUP BY ap.playerID