Combining 3 SQL queries

I am trying to combine 3 queries, but it doesn't work well. Separately they work very well. It is about the following. I want to search for all games the user has participated in, user - '$POSTCharID'

Per game points can be earned with home and away team. And here it goes wrong. The points do not match the given data.

It is checked which player is in which team, has participated and is ready. Didn't participate and not ready - 1ptn Did participate and not ready - 5ptn Didn't participate and ready - 10ptn Did participate and ready - 15ptn

Table - GameParti | GameParti_Lnk_ID | CreationDate | LeagueGames_ID | CharacterID | SupportTeam | CreatedByAccountID | CreatedByCharacterID | |:----------------:|:----------------:|:--------------:|:-----------:|:-----------:|:------------------:|:--------------------:| | 19 | 24/03/2025 9:54 | 9 | 20 | 3 | 11 | 20 | | 21 | 24/03/2025 22:12 | 9 | 31 | 3 | 11 | 31 | | 22 | 25/03/2025 9:54 | 9 | 12 | 3 | 11 | 12 | | 23 | 26/03/2025 17:49 | 8 | 12 | 3 | 11 | 12 |

Table - LeagueGames | LeagueGames_ID | CreationDate | Score_HomeTeam | Home_ClubID | Home_ClubName | Home_TeamID | Home_TeamName | Score_AwayTeam | Away_ClubID | Away_ClubName | Away_TeamID | Away_TeamName | LeagueGame_Status | |:--------------:|:----------------:|:--------------:|:-----------:|:-------------:|:-----------:|:-------------:|:--------------:|:-----------:|:-------------:|:-----------:|:-------------:|:-----------------:| | 8 | 31/01/2025 16:51 | 2 | 2 | Club Talo | 3 | Team Gholo | 1 | 14 | Club Mipu | 38 | Team Vruup | 2 | | 9 | 13/03/2025 14:14 | 0 | 3 | Club Froly | 11 | Team Hiruo | 2 | 2 | Club Talo | 3 | Team Gholo | 4 |

Table - Teams | TeamID | Club_ID | Club_Name | Team_Name | |:------:|:-------:|:----------:|:----------:| | 3 | 2 | Club Talo | Team Gholo | | 11 | 3 | Club Froly | Team Hiruo | | 38 | 14 | Club Mipu | Team Vruup |

Table - Clubs | ClubID | Clubname | |:------:|:----------:| | 2 | Club Talo | | 3 | Club Froly | | 14 | Club Mipu |

Table - TeamPlayerLnk | TeamPlayers_Lnk_ID | CharacterID | Teamplayer_ClubID | Teamplayer_TeamID | ActLinked | |:------------------:|:-----------:|:-----------------:|:-----------------:|-----------| | 2 | 59 | 2 | 3 | 1 | | 3 | 60 | 2 | 3 | 1 | | 4 | 61 | 2 | 3 | 1 | | 5 | 63 | 2 | 3 | 1 | | 6 | 64 | 3 | 11 | 1 | | 7 | 65 | 3 | 11 | 1 | | 8 | 66 | 2 | 3 | 1 | | 9 | 67 | 2 | 3 | 1 | | 10 | 68 | 2 | 3 | 1 |

Table - Characters | AccountID | ID | Firstname | LastName | |:---------:|:--:|:--------------:|:------------:| | 6 | 11 | FirstPlayer 1 | LastPlayer1 | | 11 | 12 | FirstPlayer 2 | LastPlayer2 | | 5 | 13 | FirstPlayer 3 | LastPlayer3 | | 11 | 20 | FirstPlayer 4 | LastPlayer4 | | 11 | 62 | FirstPlayer 5 | LastPlayer5 | | 0 | 53 | FirstPlayer 6 | LastPlayer6 | | 23 | 25 | FirstPlayer 7 | LastPlayer7 | | 22 | 30 | FirstPlayer 8 | LastPlayer8 | | 11 | 31 | FirstPlayer 9 | LastPlayer9 | | 0 | 60 | FirstPlayer 10 | LastPlayer10 | | 11 | 61 | FirstPlayer 11 | LastPlayer11 | | 0 | 54 | FirstPlayer 12 | LastPlayer12 | | 0 | 52 | FirstPlayer 13 | LastPlayer13 | | 0 | 51 | FirstPlayer 14 | LastPlayer14 | | 0 | 55 | FirstPlayer 15 | LastPlayer15 | | 11 | 56 | FirstPlayer 16 | LastPlayer16 | | 11 | 59 | FirstPlayer 17 | LastPlayer17 | | 11 | 63 | FirstPlayer 18 | LastPlayer18 | | 23 | 64 | FirstPlayer 19 | LastPlayer19 | | 0 | 65 | FirstPlayer 20 | LastPlayer20 | | 0 | 66 | FirstPlayer 21 | LastPlayer21 | | 0 | 67 | FirstPlayer 22 | LastPlayer22 | | 0 | 69 | FirstPlayer 23 | LastPlayer23 |

Table - PlayerReady | PlayerReady_ID | CreatedByCharacterID | |:--------------:|:--------------------:| | 61 | 20 | | 60 | 20 | | 59 | 20 | | 59 | 31 | | 61 | 25 | | 65 | 20 | | 64 | 25 | | 65 | 25 | | 60 | 25 | | 64 | 20 | | 67 | 25 | | 59 | 12 |

$POSTCharID - 20 - Result Now - Points don't match | CreationDate | hScore | hClubName | hTeamName | aScore | aClubName | aTeamName | sGameStatus | HomeTotalPts | AwayTotalPts | |:----------------:|:------:|:----------:|:----------:|:------:|:---------:|:----------:|:-----------:|:------------:|:------------:| | 13/03/2025 14:14 | 0 | Club Froly | Team Hiruo | 2 | Club Talo | Team Gholo | 4 | 350 | 192 |

$POSTCharID - 20 - Expected results | CreationDate | hScore | hClubName | hTeamName | aScore | aClubName | aTeamName | sGameStatus | HomeTotalPts | AwayTotalPts | |:----------------:|:------:|:----------:|:----------:|:------:|:---------:|:----------:|:-----------:|:------------:|:------------:| | 13/03/2025 14:14 | 0 | Club Froly | Team Hiruo | 2 | Club Talo | Team Gholo | 4 | 25 | 47 |

$POSTCharID - 12 - Result Now - Points don't match AND echo only 1 game instead of 2 games | CreationDate | hScore | hClubName | hTeamName | aScore | aClubName | aTeamName | sGameStatus | HomeTotalPts | AwayTotalPts | |:----------------:|:------:|:----------:|:----------:|:------:|:---------:|:----------:|:-----------:|:------------:|:------------:| | 13/03/2025 14:14 | 0 | Club Froly | Team Hiruo | 2 | Club Talo | Team Gholo | 4 | 58 | 65 |

$POSTCharID - 12 - Expected results | CreationDate | hScore | hClubName | hTeamName | aScore | aClubName | aTeamName | sGameStatus | HomeTotalPts | AwayTotalPts | |:----------------:|:------:|:----------:|:----------:|:------:|:---------:|:----------:|:-----------:|:------------:|:------------:| | 31/01/2025 16:51 | 2 | Club Talo | Team Gholo | 1 | Club Mipu | Team Vruup | 2 | 15 | 0 | | 13/03/2025 14:14 | 0 | Club Froly | Team Hiruo | 2 | Club Talo | Team Gholo | 4 | 6 | 28 |

    $stmt = $db->prepare("SELECT    s.CreationDate          AS sCreationDate,
                                        s.Score_HomeTeam        AS hScore,
                                        hc.Clubname    AS hClubName,
                                        ht.Team_Name            AS hTeamName,
                                        s.Score_AwayTeam        AS aScore,
                                        ac.Clubname    AS aClubName,
                                        at.Team_Name            AS aTeamName,
                                        s.LeagueGame_Status     AS sGameStatus,
                                        sum(CASE    WHEN z1.LeagueGames_ID IS NULL AND p1.PlayerReady_ID IS NULL THEN '1' 
                                                    WHEN z1.LeagueGames_ID IS NOT NULL AND p1.PlayerReady_ID IS NULL THEN '5' 
                                                    WHEN z1.LeagueGames_ID IS NULL AND p1.PlayerReady_ID IS NOT NULL THEN '10' 
                                                    WHEN z1.LeagueGames_ID IS NOT NULL AND p1.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS HomeTotalPts,
                                        sum(CASE    WHEN z2.LeagueGames_ID IS NULL AND p2.PlayerReady_ID IS NULL THEN '1' 
                                                    WHEN z2.LeagueGames_ID IS NOT NULL AND p2.PlayerReady_ID IS NULL THEN '5' 
                                                    WHEN z2.LeagueGames_ID IS NULL AND p2.PlayerReady_ID IS NOT NULL THEN '10' 
                                                    WHEN z2.LeagueGames_ID IS NOT NULL AND p2.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS AwayTotalPts                       
                                FROM        GameParti e
                                LEFT JOIN   LeagueGames s           ON s.LeagueGames_ID = e.LeagueGames_ID
                                LEFT JOIN   Teams ht     ON ht.TeamID = s.Home_TeamID
                                LEFT JOIN   Clubs hc          ON hc.ClubID = ht.Club_ID 
                                LEFT JOIN   Teams at     ON at.TeamID = s.Away_TeamID
                                LEFT JOIN   Clubs ac          ON ac.ClubID = at.Club_ID 
    
                                LEFT JOIN   TeamPlayerLnk g1     ON g1.Teamplayer_TeamID = ht.TeamID AND g1.ActLinked = '1'
                                LEFT JOIN   Characters c1              ON c1.ID = g1.CharacterID 
                                LEFT JOIN   GameParti z1           ON z1.CharacterID = g1.CharacterID AND z1.LeagueGames_ID = s.LeagueGames_ID
                                LEFT JOIN   PlayerReady p1   ON p1.PlayerReady_ID = g1.CharacterID AND p1.ActLinked = '1' AND p1.CreatedByCharacterID='$POSTCharID'
                                
                                LEFT JOIN   TeamPlayerLnk g2     ON g2.Teamplayer_TeamID = at.TeamID AND g2.ActLinked = '1'
                                LEFT JOIN   Characters c2              ON c2.ID = g2.CharacterID 
                                LEFT JOIN   GameParti z2           ON z2.CharacterID = g2.CharacterID AND z2.LeagueGames_ID = s.LeagueGames_ID
                                LEFT JOIN   PlayerReady p2   ON p2.PlayerReady_ID = g2.CharacterID AND p2.ActLinked = '1' AND p2.CreatedByCharacterID='$POSTCharID'
                                WHERE e.CharacterID = '$POSTCharID' ORDER BY CreationDate");

Thanks for your help!