MySQL: I need to return rows according to certain conditions I have a 3 tables: work, recording, release

1 work can have multiple recordings and 1 recording appears in only 1 release and 1 release can be in m recordings.

TABLE: work

+---------+-----------+
| work_id | name      |
+---------+-----------+
| 1       | Hello     | 
| 3       | Luna      | 
| 4       | Feel good | 
| 5       | My self   | 
+---------+-----------+


TABLE: recording

+---------------------------------------------------------------------+
| recording_id | work_id | release_id | name        | is_art | is_vid |
+---------------------------------------------------------------------+
| 45           | 1       | 45         | Hello4      | 1      | 0      |
| 78           | 3       | 67         | Luna5       | 1      | 0      |
| 23           | 5       | 128        | My self (r) | 1      | 0      |
| 95           | 5       | 156        | My self II  | 1      | 0      |
| 17           | 4       | 67         | Luna67      | 1      | 0      |
+---------------------------------------------------------------------+


TABLE: release

+--------------------------------------------+
| release_id | name    | year | month | day  |
+--------------------------------------------+
| 45         | Yo      | 1998 | 12    | NULL |
| 67         | Testing | 1967 | 3     | 3    |
| 128        | Maybe   | 2018 | 10    | 21   |
| 156        | Again   | 2018 | 10    | NULL |
+--------------------------------------------+


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). I could be that a recording release can have the same year, month and day. In that case I guess I need to find a unique identifier, so ill go with latest release_id

result set should look like:

+---------+---------------------------------------+
| work_id | name      | recording_id | name       |
+---------+---------------------------------------+
| 1       | Hello     | 45           | Hello4     |
| 3       | Luna      | 78           | Luna5      |
| 5       | My self   | 23           | My self (r)|
+---------+---------------------------------------+


So far, I created this query, but, honestly, as a newbie, I know its all wrong. It return duplicate rows. I fell like I need to use group by and subqueries but after 2 days of searching and testing stuff, I cannot create a solution... im going crazy

SELECT
    w.work_id, w.`name`, r.recording_id, r.`name`
FROM
    work w
    JOIN recording r on w.work_id = r.work_id
    JOIN `release`rl ON r.release_id = rl.release_id 
WHERE
    r.is_art = 1 and r.is_vid = 0
ORDER BY w.work_id, rl.released_date_year, rl.released_date_month, rl.released_date_day
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' ...