MYSQL LEFT JOIN returning all data as NULL

Question

My mysql version is 5.7.32.

I realize this has been asked many times, and I've tried many post answer without succeeding. Thank you in advance.

This is my query at the moment, which returns all from LEFT JOIN as NULL.

        SELECT %playlists%.*, tracks.*
        FROM %playlists%
        LEFT JOIN (
            SELECT *
            FROM %tracks%
            ORDER BY timestamp DESC
            LIMIT 1
        ) AS tracks ON tracks.id_playlist=%playlists%.id
        WHERE %playlists%.owner='.$id_owner.'
        ORDER BY %playlists%.name ASC

My tables are ex

%playlist%
name           |id |owner|
relaxing music | 1 | 3   |

%tracks%
id_playlist|timestamp |tracks|
   1       |1234958574| 200
   1       |1293646887| 300

I want to include the latest timestamp from %tracks%

Answer 1

I want to include the latest timestamp from %tracks%

In MySQL 5.7, I would recommend filtering the left join with a correlated subquery that brings the latest timestamp for the current playlist:

select p.*, t.timestamp, t.tracks
from playlists p
left join tracks t 
    on  t.id_playlist = p.id
    and t.timestamp = (select max(t1.timestamp) from tracks t1 where t1.id_playlist = p.id)
where p.owner = ?
order by p.name

Note that I removed the percent signs around the table names (that's not valid SQL), and that I used table aliases (p and t), which make the query easier to write and read. I also used a placeholder (?) to represent the query parameter; concatenating variables in the query string is bad practice, prepared statements should be preferred.