SHARE
TWEET

Untitled

a guest Jul 15th, 2019 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. table ISIN
  2. +---------------+----+------+
  3. | isin          | id | code |
  4. +---------------+----+------+
  5. | US0378331005  |  1 | NULL |
  6. | AU0000XVGZA3  |  2 | z    |
  7. | GB0002634946  |  3 | y    |
  8. +---------------+----+------+
  9.  
  10. table additionalCredit
  11. +------+----+
  12. | code | id |
  13. +------+----+
  14. | h    |  1 |
  15. | i    |  2 |
  16. +------+----+
  17.  
  18. table codes
  19. +--------+------+----------------------+
  20. | codeId | code | description          |
  21. +--------+------+----------------------+
  22. |   9    | h    | ETM - Principal Only |
  23. |   9    | i    | ETM - Waiting Close  |
  24. |   8    | z    | No Redemption        |
  25. |   8    | y    | Partially Prerefunded|
  26. +--------+------+----------------------+
  27.      
  28. +---------------+-----------------------+----------------------+
  29. | isin          | type8                 | type9                |
  30. +---------------+-----------------------+----------------------+
  31. | US0378331005  | null                  | ETM - Principal Only |
  32. | AU0000XVGZA3  | No Redemption         | ETM - Waiting Close  |
  33. | GB0002634946  | Partially Prerefunded | null                 |
  34. +---------------+-----------------------+----------------------+
  35.      
  36. select
  37. ISIN.isin,
  38. min(type8), min(type9)
  39. from
  40. (select
  41. ISIN.isin,
  42. case when codes.codeId=8 then codes.description end as type8,
  43. case when codes.codeId=9 then codes.description end as type9
  44. from ISIN
  45. left join codes  
  46. on ISIN.code=codes.code
  47.  
  48. union
  49. select
  50. ISIN.isin,
  51. case when codes.codeId=8 then codes.description end as type8,
  52. case when codes.codeId=9 then codes.description end as type9
  53. from ISIN
  54. left join additionalCredit ac
  55. on ac.id=isin.id
  56. left join codes
  57. on codes.code=ac.code) as n
  58. group by n.name
  59.      
  60. select name
  61.     , min(if(cid = 8, `desc`, null)) Type8
  62.     , min(if(cid = 9, `desc`, null)) Type9
  63. from (
  64.     select cid, code, `desc`, ifnull(name1, name2) name from (
  65.         select c.cid, c.code, c.desc, n1.name name1, n2.name name2
  66.         from c
  67.         left join n n1 on n1.code = c.code
  68.         left join ac on ac.code = c.code
  69.         left join n n2 on n2.id = ac.id
  70.     ) q1
  71. ) p1
  72. group by name
  73. order by name
  74. ;
  75.      
  76. name      Type8   Type9
  77. bar       descz   desci
  78. baz       descy  
  79. foo               desch
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top