2
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – dfhwze Jul 12 at 21:25
  • 1
    \$\begingroup\$ The schema and the sample data seem obfuscated. Could you provide more background and motivation for what this code does? See How to Ask. \$\endgroup\$ – 200_success Jul 13 at 0:41
  • \$\begingroup\$ 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. \$\endgroup\$ – KIKO Software Jul 13 at 8:34
  • \$\begingroup\$ @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. \$\endgroup\$ – enharmonic Jul 15 at 22:48
  • \$\begingroup\$ @200_success Nice edit of answer and fiddle ;-) \$\endgroup\$ – dfhwze Jul 16 at 4:12
3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Nice of you to update your answer with my changes to the original question; sorry to make more work for you! \$\endgroup\$ – enharmonic Jul 16 at 21:23
  • 1
    \$\begingroup\$ @200_success courtesy of the community to keep the quality up. \$\endgroup\$ – dfhwze Jul 17 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.