table ISIN
+---------------+----+------+
| isin          | id | code |
+---------------+----+------+
| US0378331005  |  1 | NULL |
| AU0000XVGZA3  |  2 | z    |
| GB0002634946  |  3 | y    |
+---------------+----+------+

table additionalCredit
+------+----+
| code | id |
+------+----+
| h    |  1 |
| i    |  2 |
+------+----+

table codes
+--------+------+----------------------+
| codeId | code | description          |
+--------+------+----------------------+
|   9    | h    | ETM - Principal Only |
|   9    | i    | ETM - Waiting Close  |
|   8    | z    | No Redemption        |
|   8    | y    | Partially Prerefunded|
+--------+------+----------------------+

+---------------+-----------------------+----------------------+
| isin          | type8                 | type9                |
+---------------+-----------------------+----------------------+
| US0378331005  | null                  | ETM - Principal Only |
| AU0000XVGZA3  | No Redemption         | ETM - Waiting Close  |
| GB0002634946  | Partially Prerefunded | null                 |
+---------------+-----------------------+----------------------+

select
ISIN.isin,
min(type8), min(type9)
from
(select
ISIN.isin,
case when codes.codeId=8 then codes.description end as type8,
case when codes.codeId=9 then codes.description end as type9
from ISIN
left join codes
on ISIN.code=codes.code

union
select
ISIN.isin,
case when codes.codeId=8 then codes.description end as type8,
case when codes.codeId=9 then codes.description end as type9
from ISIN
left join additionalCredit ac
on ac.id=isin.id
left join codes
on codes.code=ac.code) as n
group by n.name

select name
    , min(if(cid = 8, `desc`, null)) Type8
    , min(if(cid = 9, `desc`, null)) Type9
from (
    select cid, code, `desc`, ifnull(name1, name2) name from (
        select c.cid, c.code, c.desc, n1.name name1, n2.name name2
        from c
        left join n n1 on n1.code = c.code
        left join ac on ac.code = c.code
        left join n n2 on n2.id = ac.id
    ) q1
) p1
group by name
order by name
;

name      Type8   Type9
bar       descz   desci
baz       descy
foo               desch