1
select 
    sp2.sale_id, 
    sum(sp2.payment_amount) as sumpaid, 
    sik2.quantity_purchased, 
    sik2.item_kit_unit_price,
    sik2.discount_percent,  
    min(sp2.payment_date) as payment_date 
from 
    sales_payments sp2, 
    sales_item_kits sik2, 
    sales_item_kits_taxes sit2
where 
    sik2.sale_id=sp2.sale_id
    and sit2.sale_id=sp2.sale_id
group by 
    sp2.sale_id

Please help simplifying this query with (OUTER/LEFT?) JOIN which all of table doesn't have same rows amount.. thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Devi Sisca
  • 11
  • 1
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Jan 11 '15 at 18:01

1 Answers1

0

i did not get why are you using skt2 table.

select 
    sp2.sale_id, 
    sum(sp2.payment_amount) as sumpaid, 
    sik2.quantity_purchased, 
    sik2.item_kit_unit_price,
    sik2.discount_percent,  
    min(sp2.payment_date) as payment_date 
from 
    sales_payments sp2 LEFT JOIN  
    sales_item_kits sik2 ON sik2.sale_id=sp2.sale_id LEFT JOIN 
    sales_item_kits_taxes sit2 ON sit2.sale_id=sp2.sale_id

group by 
    sp2.sale_id
Riad
  • 3,822
  • 5
  • 28
  • 39
  • you can see it here: http://stackoverflow.com/questions/27888515/ruby-on-rails-display-half-a-star-for-a-decimal-rating-e-g-4-5#answer-27888761 – Riad Jan 11 '15 at 18:22