SELECT query over 4 tables including MAX function in JOIN

Question

I have 4 tables in a mysql database:

Results

Result_ID	Season_ID
1	1
2	1
4	2
4	2
…	…
37	5
38	5

Players

Player_ID | Player | Player_Type    | Player_Order
1           Fred     Defender-Left   1
2           Bert     Defender-Left   1
3           Joe      Defender-Right  2
4           Harry    Midfielder      3
5           Simon    Midfielder      3
6           Tate     Striker         4
7           Graeme   Striker         4
8           Jeff     Keeper          5
…           …        …               …

PointTypes

PointType_ID | L1_Value   | L2_Value    | Point_Value | L1_Order
1              Appearance   Keeper        1             1
2              Appearance   Outfield      1             1
3              Goal         Keeper        4             1
4              Goal         Outfield      4             1
5              Assist       Keeper        3             1
6              Assist       Outfield      3             2
…              …            …             …             …

Points

Point_ID | Player_ID | Result_ID | PointType_ID
1          1           1           1
2          2           1           2
3          3           1           2
…          …           …           …
1          1           37          1
2          2           37          2
3          3           37          2
2          4           37          2
3          6           37          2
4          4           37          6
6          6           37          4
1          1           38          1
2          2           38          2
3          3           38          2
2          4           38          2
3          5           38          2
4          2           38          4
6          5           38          6

I'm trying to run a query that produces the following result set:

Player_Order | Player_Type   | Player_ID | Player_Name | Sum(Point_Value) | Season_ID
1              Defender-Left   1           Fred          2                  5
1              Defender-Left   2           Bert          6                  5
2              Defender-Right  3           Joe           2                  5
3              Midfielder      4           Harry         5                  5
3              Midfielder      5           Simon         4                  5
4              Striker         6           Tate          5                  5
…              …               …           …             …                  …

The following query provides the all data I want (excluding the Season_ID field).

SELECT Players.Player_Order, Players.Player_Type, Players.Player_ID, Players.Player, SUM(PointTypes.Point_Value) AS Points
FROM Points, Players, PointTypes AS PT
WHERE Players.Player_ID = Points.Player_ID AND Points.PointType_ID = PointType.PointType_ID
GROUP BY Player_ID
ORDER BY Player_Order, Points DESC, Player;

How do I update the query (or even use a different type of query) to filter the data to show the same fields but adding a filter to show only the results where Results.Season_ID = Max(Results.Season_ID).

I have approved it this time, but 1) you didn't tag mysql you just wrote it in the question - I have now tagged it. 2) you didn't use table markdown, again I have converted your first lot of data into table markdown, I highly encourage you to convert the rest. — Dale K
– Dale K, Commented Aug 21 at 6:55
Tip of the day: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh
– jarlh, Commented Aug 21 at 7:07
BTW, comma joins (implicit joins) are no longer best practice. You should learn how to use proper join syntax. — Dale K
– Dale K, Commented Aug 21 at 7:07
Isn't this a trivial subquery to fetch the latest season_id (and of course correct JOINs)? db<>fiddle? — Jonas Metzler
– Jonas Metzler, Commented Aug 21 at 7:13
You can't use aggregation functions in WHERE, since aggregation happens after selecting rows. You have to do it in HAVING. — Barmar
– Barmar, Commented Aug 21 at 14:22

Michał Turczyn · Accepted Answer · 2025-08-22 06:58:04Z

1

Here's query that you could try with inline comments for clarity

SELECT 
    p.Player_Order,
    p.Player_Type,
    p.Player_ID,
    p.Player AS Player_Name,
    SUM(pt.Point_Value) AS Points,
    r.Season_ID
FROM Points po
JOIN Players p 
    ON p.Player_ID = po.Player_ID
JOIN PointTypes pt 
    ON po.PointType_ID = pt.PointType_ID
JOIN Results r 
    ON po.Result_ID = r.Result_ID
-- only include the latest season (max Season_ID)
WHERE r.Season_ID = (
    SELECT MAX(Season_ID) 
    FROM Results
)
-- group by all player/season info to aggregate points correctly
GROUP BY 
    p.Player_Order,
    p.Player_Type,
    p.Player_ID,
    p.Player,
    r.Season_ID
ORDER BY 
    p.Player_Order,
    Points DESC,
    p.Player;

DB fiddle

answered Aug 22 at 6:58

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SELECT query over 4 tables including MAX function in JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related