Re: Help : Sum 2 tables based on key from other table

If the tables aren't huge, you're not concerned about optimization, and you just want to get your numbers, I think something like this would do the trick.  I haven't actually tried it 'cause I didn't have easy access to your tables:

SELECT 

  a.product_id,

  a.product_name,

  b.initial_stock_sum,

  c.in_out_sum,

  c.in_sum,

  c.out_sum

FROM

  a

LEFT JOIN

  (SELECT

    product_id,

    SUM(initial_stock) AS initial_stock_sum

  FROM b

  GROUP BY product_id

  ) b USING (product_id)

LEFT JOIN

  (SELECT

    product_id,

    sum(CASE WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0 END) AS in_out_sum,

    sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN in ELSE 0 END) AS in_sum,

    sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN out ELSE 0 END) AS out_sum

   FROM c

   GROUP BY product_id

   ) c USING (product_id)

WHERE a.supplier_id='XXX';

Cheers,

Ken