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)?

• dfhwze: You asked this on SE and CR. But the best place to ask questions like this is dba.stackexchange.com. And thanks alot for providing a Fiddle. This makes a review much easier.
• 200_success: The schema and the sample data seem obfuscated. Could you provide more background and motivation for what this code does? See How to Ask.
• KIKO Software: I agree with 200_success. I always find it a lot easier to work with tables and data that mean something to me. The db-fiddle did not help much either. The only thing I know now is that you're not using indexes.
• enharmonic: @200_success I deobfuscated the sample, but as you can see, it does not really add too much to the question (and might unnecessarily complicate it). I'm not in control of the underlying schema.
• dfhwze: @200_success Nice edit of answer and fiddle ;-)

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

• enharmonic: Nice of you to update your answer with my changes to the original question; sorry to make more work for you!
• dfhwze: @200_success courtesy of the community to keep the quality up.