mariadb - Combining 3 SQL queries - Stack Overflow

admin 管理员组

文章数量: 1086019

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
12	17/03/2025 14:39	9	61	3	11	61
13	18/03/2025 21:42	9	56	3	11	56
14	19/03/2025 12:15	9	62	3	11	62
15	19/03/2025 12:40	9	64	3	23	64
16	19/03/2025 12:42	9	63	3	11	63
17	21/03/2025 12:56	9	59	3	11	59
20	24/03/2025 16:36	9	25	3	23	25
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

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
12	17/03/2025 14:39	9	61	3	11	61
13	18/03/2025 21:42	9	56	3	11	56
14	19/03/2025 12:15	9	62	3	11	62
15	19/03/2025 12:40	9	64	3	23	64
16	19/03/2025 12:42	9	63	3	11	63
17	21/03/2025 12:56	9	59	3	11	59
20	24/03/2025 16:36	9	25	3	23	25
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	ActLinked
61	20	1
60	20	1
59	20	1
59	31	1
61	25	1
65	20	1
64	25	1
65	25	1
60	25	1
64	20	1
67	25	1
59	12	1

$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

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

Teamplayer ID	participated	Ready	Points
64	1	0	5
65	0	0	1

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

Teamplayer ID	participated	Ready	Points
59	1	1	15
61	1	0	5
63	1	0	5
60	0	0	1
66	0	0	1
67	0	0	1

Thanks for your help!

• sql
• mariadb

Share Improve this question Follow edited Mar 28 at 18:43 Barmar 784k5757 gold badges548548 silver badges660660 bronze badges asked Mar 27 at 9:29 Dimko 696Dimko 696 3511 silver badge66 bronze badges 11

• Your problem probably 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. – Guillaume Outters Commented Mar 27 at 9:57
• Your PlayerReady table does not include any ActLinked column, however your have two tests p1.ActLinked = '1' and p2.ActLinked = '1'. They do not look important in the process (I suppose they all are set to 1), but it would be necessary to add it to have a fully reproducible example. – Guillaume Outters Commented Mar 27 at 10:09
• Yes indeed, I fot the ActLinked column in the post. But it is present in the database. It has been adjusted in the post. How could I achieve that with "multiply by m the sum of the n-rows table, and by n the sum of the m-rows table" – Dimko 696 Commented Mar 27 at 10:28
• Start with just one table and check the query returns the expected record(s). Then add the next table and check the result. Repeat until you find the issue. Decide what the correct business logic is for the specific issue and implement that in your SQL – NickW Commented Mar 27 at 10:42
• 1 Also, please tag your question with the DBMS you are using. I'm guessing that it's an old version of mySQL - as every other DBMS that I'm aware of would require a GROUP BY clause in your SQL – NickW Commented Mar 27 at 10:44

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 4

• 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;

characterid	screationdate	hscore	hclubname	hteamname	ascore	aclubname	ateamname	sgamestatus	hometotalpts	awaytotalpts
12	13/03/2025 14:14	0	Club Froly	Team Hiruo	2	Club Talo	Team Gholo	4	6	28
12	31/01/2025 16:51	2	Club Talo	Team Gholo	1			2	15
20	13/03/2025 14:14	0	Club Froly	Team Hiruo	2	Club Talo	Team Gholo	4	25	47

(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

本文标签： mariadbCombining 3 SQL queriesStack Overflow