MySQL: I need to return rows according to certain conditions
Step by step:


  Basically, for each work, I want to return the recording where is_art
  = 1 and is_vid = 0 AND were the release is the oldest (oldest year, month and date). Sometimes, the release can have the same year, month
  and day, so, in that case, I guess I need to find a unique identifier,
  so ill go with latest release_id


SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id 
WHERE r.is_art = 1 and r.is_vid = 0;


With above you will get multiple lines for 1 recording, when there is more than 1 release. We can sort, and take the first record.

SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id 
WHERE r.is_art = 1 and r.is_vid = 0
ORDER by s.year DESC, s.month DESC, s.day DESC
LIMIT 1;


This should get all releases, which can be linked to work:

SELECT w.work_id, w.name, rr.recording_id, rr.name
FROM work w
INNER JOIN (
    SELECT r.recording_id, r.name, s.year, s.month, s.day
    FROM recording r
    INNER JOIN releases s on s.release_id = r.release_id 
    WHERE r.is_art = 1 and r.is_vid = 0
    ORDER by s.year DESC, s.month DESC, s.day DESC
    LIMIT 1
) rr on rr.work_id = w.work_id


Above is untested, so change if needed... 

untested, and indeed it is/was wrong, so the changed query to return the recording and release info is:

SELECT r.recording_id, 
       r.name, 
       r.work_id, 
       MAX(s.year) as year, 
       MAX(s.month) as month, 
       MAX(s.day) as day
    FROM `recording` r
    INNER JOIN `release` s on s.release_id = r.release_id 
    WHERE r.is_art = 1 and r.is_vid = 0
    GROUP BY r.recording_id, r.name, r.work_id
    ;


Above is wrong! Is there is one release with a recording in (yyyy/mm) '2020/05' and '2019/06' the month returned for that recording will be '2020/06' ...