Inventory SQL Query without UNION?

There are too many possibilities to solve this, what exactly do you want from these reports?

the buy orders? the products? the inputs/outputs?

(I'm posting this as an answer because I cannot comment on your question, I'll update with answer if you could please enlighten me)

UPDATE

try this

#on inputnamed_scope :by_product, lambda {|id| {:joins => {:orders => {:buy_leads => :products}}, :conditions => ['products.id = ?', id]}}

and you can get the inputs that match that product id by calling

Input.by_product(25)

if that is what you were looking for I think you can manage to make the outputs by products too now :]

I cannot test this without data but I think It should be something like this:

SELECT        b.*, p.*, i.order_id, i.id AS input_id, i.quantity AS quantity, -o.quantity AS quantity,        (i.quantity - COALESCE(o.quantity,0)) AS remaining_qtyFROM        products p        JOIN buy_leads b ON b.product_id = p.id        JOIN orders r ON r.buy_lead_id = b.id        JOIN inputs i ON i.order_id = r.id        LEFT JOIN outputs o ON o.input_id = i.id

Victor's solution fails when there are multiple "output" records because the join will duplicate the products by BOTH the inputs and the outputs.

Instead, you should JOIN using a derived table instead of the actual table. Without data this is difficult to test and demonstrate but you should be trying something like this:

    "SELECT b.*, p.*, i.order_id,        i.id AS input_id,        i.quantity AS quantity,ISNULL(z.negquantities,0) negquantities,i.quantity + ISNULL(z.negquantities,0) sumquantities FROM inputs i      JOIN orders r ON r.id = i.order_id      JOIN buy_leads b ON b.id = r.buy_lead_id      JOIN products p ON p.id = b.product_id  JOIN     (SELECT SUM(-1 * o.quantity) NegQuantities, o.input_id FROM outputs o GROUP BY o.input_id) z    ON z.input_id = i.id

You see that you are joining the aggregate sum of the output table, grouped by the input id, rather than the output table itself. This eliminates unintended row duplications in your joins.Of course, you can add more elements to the "ON" list or the "Where" clause of the derived table (which I called "z") This should get you most of the way there. Alternatively, post up a DB diagram so we understand your table relationships better.