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

Table - LeagueGames

Table - Teams

Table - Clubs

Table - TeamPlayerLnk

Table - Characters

Table - PlayerReady

$POSTCharID - 20 - Result Now - Points don't match

$POSTCharID - 20 - Expected results

$POSTCharID - 12 - Result Now - Points don't match AND echo only 1 game instead of 2 games

$POSTCharID - 12 - Expected results

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;

This is the data I get when I run the query separately.

POSTCharID 12 and game 9 – Hometeam Player Points - Total 6

POSTCharID 12 and game 9 – Awayteam Player Points - Total 28

Thanks for your help!

• Your problem lies in that joining your main table to subtables that have respectively m and n rows for 1 row in your main table, will multiply by m the sum of the n-rows table, and by n the sum of the m-rows table.
• There is no interaction between your Home and Away join groups until they are joined to LeagueGames, so you could compute them separately in two Common Table Expressions, before joining them to LeagueGames in your final query
• Additionally, they are fully symetrical: no need of 2 CTEs, you can mutualize them in 1 CTE

WITH
-- Protect against duplicates (20 that participated twice to the same game under IDs 11 and 19);
-- if we do not, our results will be multiplied by 2.
parti AS (SELECT DISTINCT LeagueGames_ID, CharacterID FROM GameParti),
points AS
(
    SELECT
    c.Clubname,
    t.TeamID,
    t.Team_Name,
    e.LeagueGames_ID,
    e.CharacterID CreatedByCharacterID,
    sum(CASE    WHEN z.LeagueGames_ID IS NULL AND p.PlayerReady_ID IS NULL THEN '1'
                WHEN z.LeagueGames_ID IS NOT NULL AND p.PlayerReady_ID IS NULL THEN '5'
                WHEN z.LeagueGames_ID IS NULL AND p.PlayerReady_ID IS NOT NULL THEN '10'
                WHEN z.LeagueGames_ID IS NOT NULL AND p.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS TotalPts
    FROM        parti e
    JOIN        LeagueGames s    ON s.LeagueGames_ID = e.LeagueGames_ID
    JOIN        Teams t          ON t.TeamID IN (s.Home_TeamID, s.Away_TeamID)
    JOIN        Clubs c          ON c.ClubID = t.Club_ID
    JOIN        TeamPlayerLnk g  ON g.Teamplayer_TeamID = t.TeamID AND g.ActLinked = '1'
    JOIN        Characters ch    ON ch.ID = g.CharacterID
    LEFT JOIN   GameParti z      ON z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID
    LEFT JOIN   PlayerReady p    ON p.PlayerReady_ID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = e.CharacterID
    GROUP BY 1, 2, 3, 4, 5
)
SELECT
    e.CharacterID,
    s.CreationDate          AS sCreationDate,
    s.Score_HomeTeam        AS hScore,
    ht.Clubname             AS hClubName,
    ht.Team_Name            AS hTeamName,
    s.Score_AwayTeam        AS aScore,
    at.Clubname             AS aClubName,
    at.Team_Name            AS aTeamName,
    s.LeagueGame_Status     AS sGameStatus,
    ht.TotalPts             AS HomeTotalPts,
    at.TotalPts             AS AwayTotalPts
FROM        parti e
LEFT JOIN   LeagueGames s           ON s.LeagueGames_ID = e.LeagueGames_ID
LEFT JOIN   points ht     ON ht.TeamID = s.Home_TeamID AND ht.LeagueGames_ID = e.LeagueGames_ID AND ht.CreatedByCharacterID = e.CharacterID
LEFT JOIN   points at     ON at.TeamID = s.Away_TeamID AND at.LeagueGames_ID = e.LeagueGames_ID AND at.CreatedByCharacterID = e.CharacterID
WHERE e.CharacterID = '$POSTCharID'
ORDER BY 1, 2;

(see it in a fiddle)

0-player twist

Note that those results still need one additional twist on NULL-returning LEFT JOINs:

They are necessary to give 1 point to a team registered to a game, but whose players where either not participating or not ready.
But the LEFT JOIN should apply to participation or readiness of the characters, not their existence; in the particular case of Team Vruup that has literally 0 character (and thus cannot get one ready), left joining all tables will result in a "virtual character" with all values set to NULL still adding 1 point.
Thus we would get 1 instead of the intended 0 for the game against Team Gholo;
and similarly (although I didn't diagnose in details) Team Gholo received an additional point in its Away games, thus totaling 29 and 48 instead of 28 and 47.

So the LEFT JOIN are reserved for the last step, the ones ensuring the characters are 1. participating and 2. ready.
The other ones are (INNER) JOINs, ensuring that a team participating to a game exists, that the club it belongs to exists, and it has at least one player to engage.

But then, a JOIN with 0 character will result in 0 row instead of 1 with NULL characters: thus it creates a new problem: we don't even get the team's name.

This is why some twist will be necessary to either not count the additional 1, or reconstitute team and club name.

Additional need: subtract already collected points

As seen in the comments, if some points already have been attributed, they should be deduced from the total "to distribute".

As this table gets its primary key from TeamID, LeagueGameID, it should naturally be injected after points (not during points: that would add the points once per character).

But we can rely on the same technique than for points (computing independently from the fact this is the Home or the Guest team),
by adding a dedicated CTE.

We transform the query this way:

WITH points AS (…), -- No change here
points2 AS -- Add points2, which is points enriched by one new field.
(
    SELECT p.*, COALESCE(EarnedPoints, 0) AlreadyCollectedPts
    FROM points p LEFT JOIN CollectedPoints cp ON cp.LeagueGames_ID = p.LeagueGames_ID AND cp.TeamID = p.TeamID
)
SELECT
    …, -- No change until AwayTotalPts
    -- Add new fields:
    ht.TotalPts - ht.AlreadyCollectedPts AS HomePtsToCollect,
    at.TotalPts - at.AlreadyCollectedPts AS AwayPtsToCollect
FROM …
LEFT JOIN points2 ht ON … -- Instead of points
LEFT JOIN points2 at ON … -- Same here
…; -- No change