Advertisement
Guest User

Untitled

a guest
Sep 30th, 2014
179
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.81 KB | None | 0 0
  1. selection_id | group_id | selection_group_id |
  2. ------------------------------------------------
  3. 1 1 1
  4.  
  5. selector_id | selection_id | name | index
  6. ------------------------------------------
  7. 1 1 Step1 0
  8. 2 1 Step2 1
  9. 3 1 Step3 2
  10.  
  11. select selector_id
  12. from selectors s
  13. group by selector_id
  14. having max(case when name = 'Step1' then 1 else 0 end) = 1 and
  15. max(case when name = 'Step2' then 1 else 0 end) = 1 and
  16. max(case when name = 'Step3' then 1 else 0 end) = 1 and
  17. max(case when name in ('Step1', 'Step2', 'Step3') then 0 else 1 end) = 0
  18.  
  19. select selector_id
  20. from selectors s
  21. group by selector_id
  22. having count(distinct name) = 3 and
  23. max(case when name in ('Step1', 'Step2', 'Step3') then 0 else 1 end) = 0
  24.  
  25. SELECT selection_id, GROUP_CONCAT(name)
  26. FROM selectors
  27.  
  28. SELECT DISTINCT Selection_ID
  29. FROM (
  30. SELECT selection_id, GROUP_CONCAT(name) gc
  31. FROM selectors
  32. ) t
  33. WHERE gc = 'Step1,Step2,Step3'
  34.  
  35. SELECT selections.selection_id FROM `prstshp_productsort_groups_selections` selections
  36. WHERE EXISTS (SELECT * FROM `prstshp_productsort_groups_selectors` s3
  37. WHERE s3.name = "Muskelaufbau" AND s3.index = 0 AND s3.selection_id = selections.selection_id)
  38. AND
  39. EXISTS (SELECT * FROM `prstshp_productsort_groups_selectors` s4
  40. WHERE s4.name = "Mesomorph" AND s4.index = 1 AND s4.selection_id = selections.selection_id)
  41. AND
  42. EXISTS (SELECT * FROM `prstshp_productsort_groups_selectors` s5
  43. WHERE s5.name = "Männlich" AND s5.index = 2 AND s5.selection_id = selections.selection_id)
  44. AND
  45. EXISTS (SELECT * FROM `prstshp_productsort_groups_selectors` s6
  46. WHERE s6.name = "25-40" AND s6.index = 3 AND s6.selection_id = selections.selection_id)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement