Join and pivot multiple tables of securities data in MySQL
DB Design

Your tables are not correctly normalized:


codes.description depends on codes.code → requires a table with codes.code as primary key


You are also missing constraints:


ISIN.id primary key
ISIN.name unique key
additionalCredit.id, additionalCredit.code primary key
codes.codeId, codes.code primary key


Query Optimization

mysql does not come with a pivot, so we need to use an aggregate function - in this case min(..) - instead. This way we can avoid the union of redundant quasi code duplication.

Fiddle

query..

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
;


yielding..




name              Type8                 Type9
AU0000XVGZA3      No Redemption         ETM - Waiting Close
GB0002634946      Partial Prerefunded 
US0378331005                            ETM - Principal Only