MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ISIN
	(`isin` varchar(12), `id` int, `code` varchar(10));
	
CREATE TABLE additionalCredit
	(`code` varchar(10), `id` int);
	
CREATE TABLE codes
        (`codeId` int,`code` varchar(10), `description` varchar(32));
	
INSERT INTO ISIN
	(`isin`, `id`,`code`)
VALUES
	('US0378331005',1, NULL),
	('AU0000XVGZA3',2,'z'),
	('GB0002634946',3,'y');
	
INSERT INTO additionalCredit
	(`code`, `id`)
VALUES
	('h', 1),
	('i',2);
	
INSERT INTO codes
        (`codeId`,`code`,`description`)
VALUES
        (9,'h','ETM - Principal Only'),
        (9,'i','ETM - Waiting Close'),
        (8,'z','No Redemption'),
        (8,'y','Partially Prerefunded');
select codes.codeId, codes.code, codes.description, ISIN1.isin name1, ISIN2.isin name2
from codes
left join ISIN ISIN1 on ISIN1.code = codes.code
left join additionalCredit on additionalCredit.code = codes.code
left join ISIN ISIN2 on ISIN2.id = additionalCredit.id
;


codeId
code
description
name1
name2


9
h
ETM - Principal Only
null
US0378331005

9
i
ETM - Waiting Close
null
AU0000XVGZA3

8
z
No Redemption
AU0000XVGZA3
null

8
y
Partially Prerefunded
GB0002634946
null


select codeId, code, description, ifnull(name1, name2) isin from (
    select codes.codeId, codes.code, codes.description, ISIN1.isin name1, ISIN2.isin name2
    from codes
    left join ISIN ISIN1 on ISIN1.code = codes.code
    left join additionalCredit on additionalCredit.code = codes.code
    left join ISIN ISIN2 on ISIN2.id = additionalCredit.id
) q1
;


codeId
code
description
isin


9
h
ETM - Principal Only
US0378331005

9
i
ETM - Waiting Close
AU0000XVGZA3

8
z
No Redemption
AU0000XVGZA3

8
y
Partially Prerefunded
GB0002634946


select isin
    , min(if(codeId = 8, description, null)) Type8
    , min(if(codeId = 9, description, null)) Type9
from (
    select codeId, code, description, ifnull(name1, name2) isin from (
        select codes.codeId, codes.code, codes.description, ISIN1.isin name1, ISIN2.isin name2
        from codes
        left join ISIN ISIN1 on ISIN1.code = codes.code
        left join additionalCredit on additionalCredit.code = codes.code
        left join ISIN ISIN2 on ISIN2.id = additionalCredit.id
    ) q1
) p1
group by isin
order by isin
;


isin
Type8
Type9


AU0000XVGZA3
No Redemption
ETM - Waiting Close

GB0002634946
Partially Prerefunded
null

US0378331005
null
ETM - Principal Only

codeId	code	description	name1	name2
9	h	ETM - Principal Only	null	US0378331005
9	i	ETM - Waiting Close	null	AU0000XVGZA3
8	z	No Redemption	AU0000XVGZA3	null
8	y	Partially Prerefunded	GB0002634946	null