daily pastebin goal
21%
SHARE
TWEET

Untitled

a guest Apr 21st, 2018 204 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ##these 2 queries are to give you background on the issue
  2. ##this pulls all users that have records in promos_users that have promos that are promo_type='SALES'
  3.  
  4. select u.email, sp.code as sales_promo
  5. from users u
  6.  left join promos_users pu_sales on (u.id=pu_sales.user_id)
  7.   join promos sp on (sp.code=pu_sales.code and sp.promo_type='SALES')
  8. where email='zehrtrucking@wigi.us';
  9. +----------------------+-------------+
  10. | email                | sales_promo |
  11. +----------------------+-------------+
  12. | zehrtrucking@wigi.us | 50off       |
  13. +----------------------+-------------+
  14. ##how the promos_users table looks (can't use code right from this table because it is promo_type agnostic)
  15.  
  16. mysql> select * from promos_users where user_id=(select id from users where email='zehrtrucking@wigi.us');
  17. +-----+---------+--------+---------------------+
  18. | id  | user_id | code   | ts                  |
  19. +-----+---------+--------+---------------------+
  20. | 209 |   36222 | 50off  | 2008-04-18 11:54:44 |
  21. | 329 |   36222 | Bronze | 2008-04-28 15:38:06 |
  22. +-----+---------+--------+---------------------+
  23.  
  24. ##this following solution looks successful, that is indeed the output i need for a company that has a sales and saves promo, but i added in demo@loadmax.com in the where clause, no record matches!
  25. mysql> select u.email, sp.code as sales_promo, svp.code as saves_promo  
  26. from users u  
  27.  left join promos_users pu_sales on (u.id=pu_sales.user_id)    
  28.   join promos sp on (sp.code=pu_sales.code and sp.promo_type='SALES')  
  29.  left join promos_users pu_saves on (u.id=pu_saves.user_id)
  30.   join promos svp on (svp.code=pu_saves.code and svp.promo_type='SAVES')  
  31. where email in ('zehrtrucking@wigi.us','demo@loadmax.com');
  32. +----------------------+-------------+-------------+
  33. | email                | sales_promo | saves_promo |
  34. +----------------------+-------------+-------------+
  35. | zehrtrucking@wigi.us | 50off       | Bronze      |
  36. +----------------------+-------------+-------------+
  37.  
  38. 1 row in set (0.00 sec)
  39.  
  40. ##but if we the joins to promos left joins ... i get the records , but look how messed up the results are.. now i'm getting duplicates for the mail column
  41. mysql> select u.email, sp.code as sales_promo, svp.code as saves_promo  
  42. from users u  
  43.  left join promos_users pu_sales on (u.id=pu_sales.user_id)    
  44.   left join promos sp on (sp.code=pu_sales.code and sp.promo_type='SALES')  
  45.  left join promos_users pu_saves on (u.id=pu_saves.user_id)
  46.   left join promos svp on (svp.code=pu_saves.code and svp.promo_type='SAVES')  
  47. where email in ('zehrtrucking@wigi.us','demo@loadmax.com');
  48. +----------------------+-------------+-------------+
  49. | email                | sales_promo | saves_promo |
  50. +----------------------+-------------+-------------+
  51. | demo@loadmax.com     | NULL        | NULL        |
  52. | zehrtrucking@wigi.us | 50off       | NULL        |
  53. | zehrtrucking@wigi.us | 50off       | Bronze      |
  54. | zehrtrucking@wigi.us | NULL        | NULL        |
  55. | zehrtrucking@wigi.us | NULL        | Bronze      |
  56. +----------------------+-------------+-------------+
  57.  
  58. 4 rows in set (0.00 sec)
  59. ##here is the output i need
  60.  
  61. +----------------------+-------------+-------------+
  62. | email                | sales_promo | saves_promo |
  63. +----------------------+-------------+-------------+
  64. | demo@loadmax.com     | NULL        | NULL        |
  65. | zehrtrucking@wigi.us | 50off       | Bronze      |
  66. +----------------------+-------------+-------------+
  67.  
  68. ##here is the solution .. inspired by gnari (produces above output)
  69.  
  70. select u.email, sp.code as sales_promo, svp.code as saves_promo  
  71. from users u  
  72.  left join (promos_users pu_sales  join promos sp on (sp.code=pu_sales.code and sp.promo_type='SALES')) on (pu_sales.user_id=u.id)  
  73.  left join (promos_users pu_saves join promos svp on (svp.code=pu_saves.code and svp.promo_type='SAVES')) on (pu_saves.user_id=u.id)
  74. where email in ('zehrtrucking@wigi.us','demo@loadmax.com');
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