sql - MySQL Multiple Joins Causing Both Columns to Change -

i have database managing sales @ store. i’m trying write query gets total cost each invoice , total amount paid.

all payments recorded in ‘invoice_payments’ table. total cost invoice job_price + stock_unit items (products).

‘invoice’ table:

invoice_id  job_price 000071      100.00 

‘stock_unit’ table:

unit_id     price       invoice_id 000261      10      000071 000262      10      000071 000263      10      000071 

‘invoice_payments’ table:

payment_id  invoice_id  amount 000074      000071      100.00 000075      000071      30.00 

query:

select     invoice.invoice_id,      sum(stock_unit.`sold_price` + invoice.job_price) price,     sum(`invoice_payments`.`amount`) paid  invoice  left join stock_unit     on stock_unit.invoice_id = invoice.invoice_id  left join invoice_payments     on invoice_payments.invoice_id = invoice.invoice_id  group invoice.invoice_id 

the query works expected when there 1 join, when 2 added affect each other.

before add ‘invoice_payments’ join:

invoice_id  paid 000071      130.00 

after:

invoice_id  price       paid 000071      660.00      390.00 

what should be:

invoice_id  price       paid 000071      130.00      130.00 

sql fiddle isn't working correctly i've put schema here can better idea table: http://sqlfiddle.com/#!9/091a35

am better have 3 seperate queries , join those?

you should select e separated result , not calculate result od both same query joined

select t1.invoce_id, t1.price, t2.paid   (  select           invoice.invoice_id invoce_id,      sum(stock_unit.`sold_price` + invoice.job_price) price invoice left join stock_unit     on stock_unit.invoice_id = invoice.invoice_id group invoice.invoice_id ) t1  left join   ( select      invoice.invoice_id invoce_id,      sum(`invoice_payments`.`amount`) paid invoice     left join invoice_payments     on invoice_payments.invoice_id = invoice.invoice_id group invoice.invoice_id ) t2   on t1.invoce_id = t2.invoce_id