MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE work(
   work_id INTEGER  NOT NULL PRIMARY KEY 
  ,name    VARCHAR(12) NOT NULL
);
INSERT INTO work(work_id,name) VALUES (1,'Hello');
INSERT INTO work(work_id,name) VALUES (3,'Luna');
INSERT INTO work(work_id,name) VALUES (4,'Feel good');
INSERT INTO work(work_id,name) VALUES (5,'My self');
CREATE TABLE recording(
   recording_id INTEGER  NOT NULL PRIMARY KEY 
  ,work_id      INTEGER  NOT NULL
  ,release_id   INTEGER  NOT NULL
  ,name         VARCHAR(13) NOT NULL
  ,is_art       BIT  NOT NULL
  ,is_vid       BIT  NOT NULL
);
INSERT INTO recording(recording_id,work_id,release_id,name,is_art,is_vid) VALUES (45,1,45,'Hello4',1,0);
INSERT INTO recording(recording_id,work_id,release_id,name,is_art,is_vid) VALUES (78,3,67,'Luna5',1,0);
INSERT INTO recording(recording_id,work_id,release_id,name,is_art,is_vid) VALUES (23,5,128,'My self (r)',1,0);
INSERT INTO recording(recording_id,work_id,release_id,name,is_art,is_vid) VALUES (95,5,156,'My self II',1,0);
CREATE TABLE releases(
   release_id INTEGER  NOT NULL PRIMARY KEY 
  ,name       VARCHAR(9) NOT NULL
  ,year       INTEGER  NOT NULL
  ,month      INTEGER  NOT NULL
  ,day        INTEGER 
);
INSERT INTO releases(release_id,name,year,month,day) VALUES (45,'Yo',1998,12,NULL);
INSERT INTO releases(release_id,name,year,month,day) VALUES (67,'Testing',1967,3,3);
INSERT INTO releases(release_id,name,year,month,day) VALUES (128,'Maybe',2018,10,21);
INSERT INTO releases(release_id,name,year,month,day) VALUES (156,'Again',2018,10,NULL);
select work_id, work_name, recording_id, recording_name
from (
    select
        w.work_id,
        w.name work_name,
        r.recording_id,
        r.name recording_name,
        row_number() over(
            partition by r.work_id 
            order by -l.year desc, -l.month desc, -l.day desc, r.release_id desc
        ) rn
    from work w
    inner join recording r 
        on r.work_id = w.work_id
        and r.is_art = 1
        and r.is_vid = 0
    inner join releases l 
        on l.release_id = r.release_id
) t
where rn = 1



work_id
work_name
recording_id
recording_name


1
Hello
45
Hello4

3
Luna
78
Luna5

5
My self
23
My self (r)