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';
