MySQL query - Select statement from two tables with group by returning records with largest ids

Question

I really need help from you, I've spend a lot of time already on trying to figure it out but without success :(

I have two tables:

What I need is to group everything by sea_id / bat_season and gain the greatest Id's for these seasons. So bat_id's 3 & 5 should be returned with their linked data. But if there is no data in Table 2 I still should see details of two seasons without Table 2 details.

My closest result is here with the below statement:

SELECT b.bat_id, b.bat_trophies, b.bat_ranking, s.sea_id, s.sea_name, s.sea_start 
FROM gvg_seasons s 
LEFT JOIN (SELECT bat_id, bat_trophies, bat_ranking, bat_season FROM gvg_battles ORDER BY bat_id DESC LIMIT 1) b 
ON s.sea_id = b.bat_season 
WHERE s.sea_gl_id = 1
GROUP BY s.sea_id DESC

The result: Result

If someone can help me here please I will be very grateful.

Answer 1

I haven't tried this as I didn't fancy transcribing the table data from your images but it should provide the result you are looking for.

The innermost sub-query gets the max(bat_id) per bat_season. This is joined back to the gvg_battles to give the latest battle per season.

SELECT *
FROM gvg_seasons s
LEFT JOIN (
    SELECT b1.*
    FROM gvg_battles b1
    JOIN (
        SELECT bat_season, MAX(bat_id) AS max_bat_id
        FROM gvg_battles
        GROUP BY bat_season
    ) b_max ON b1.bat_id = b_max.max_bat_id
) b2 ON s.sea_id = b2.bat_season;