Join and pivot multiple tables of securities data in MySQL

Question

I'm interested in a way to join three (or more) tables in a way that is more efficient (both in terms of conciseness of code and in terms of generating the results) than what I have currently.

Underlying tables:

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|
+--------+------+----------------------+

Expected results:

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

Working Code

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

Context

...from the original Q&A at Stack Overflow that gives my thought process and steps taken, where the first commenter suggested there could be a better answer. Here's a fiddle.

Can you help me find a better way than a union statement (in which nearly the entire query is duplicated)?

Answer 1

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