clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555470 fiddles created (37472 in the last week).

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));
 hidden batch(es)


INSERT INTO ISIN (`isin`, `id`,`code`) VALUES ('US0378331005',1, NULL), ('AU0000XVGZA3',2,'z'), ('GB0002634946',3,'y');
 hidden batch(es)


INSERT INTO additionalCredit (`code`, `id`) VALUES ('h', 1), ('i',2);
 hidden batch(es)


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');
 hidden batch(es)


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 US0378331005
9 i ETM - Waiting Close AU0000XVGZA3
8 z No Redemption AU0000XVGZA3
8 y Partially Prerefunded GB0002634946
 hidden batch(es)


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
 hidden batch(es)


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
US0378331005 ETM - Principal Only
 hidden batch(es)