Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.61 KB | None | 0 0
  1. SELECT `table1.partnumber`,
  2. `table1`.`price`,
  3. `maintable`.`price`
  4. FROM `table1`
  5. INNER JOIN `maintable`
  6. ON `table1`.`partnumber` = `maintable`.`partnumber`
  7. UNION ALL
  8. SELECT `table2`.`partnumber`,
  9. `table2`.`price`,
  10. `maintable`.`price`
  11. FROM `table2`
  12. INNER JOIN `maintable`
  13. ON `table2`.`partnumber` = `maintable`.`partnumber`
  14. UNION ALL
  15. SELECT `table3.partnumber`,
  16. `table3`.`price`,
  17. `maintable`.`price`
  18. FROM `table3`
  19. INNER JOIN `maintable`
  20. ON `table3`.`partnumber` = `maintable`.`partnumber`
  21.  
  22. SELECT `partnumber`,MIN(`price`) as `price`
  23. FROM (
  24. SELECT `table1`.`partnumber`,
  25. `table1`.`price`,
  26. `maintable`.`price`
  27. FROM `table1`
  28. INNER JOIN `maintable`
  29. ON `table1`.`partnumber` = `maintable`.`partnumber`
  30. UNION ALL
  31. SELECT `table2`.`partnumber`,
  32. `table2`.`price`,
  33. `maintable`.`price`
  34. FROM `table2`
  35. INNER JOIN `maintable`
  36. ON `table2`.`partnumber` = `maintable`.`partnumber`
  37. UNION ALL
  38. SELECT `table3.partnumber`,
  39. `table3`.`price`,
  40. `maintable`.`price`
  41. FROM `table3`
  42. INNER JOIN `maintable`
  43. ON `table3`.`partnumber` = `maintable`.`partnumber`
  44. ) AS `part` GROUP BY `partnumber`
  45.  
  46. SELECT partnumber, MIN(price) FROM (
  47. SELECT 1 AS tn, partnumber, table1.price
  48. FROM table1 JOIN maintable USING (partnumber)
  49. UNION ALL
  50. SELECT 2 AS tn, partnumber, table2.price
  51. FROM table2 JOIN maintable USING (partnumber)
  52. UNION ALL
  53. SELECT 3 AS tn, partnumber, table3.price
  54. FROM table3 JOIN maintable USING (partnumber)
  55. ) t
  56. GROUP BY partnumber
  57. HAVING COUNT(DISTINCT tn) >= 2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement