Query to retrieve available stock

Question

I have a query that outputs a list of products with some fields from other tables. That query works fine.

Now, I'm attempting to retrieve the available stock for each product in that list. I'm not sure if I'm doing it correctly or in the most performant way. Here's what I've got working so far.

select distinct 
p.id, 
p.name
if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,
[other fields from joined tables]
from products p 
[a bunch of inner joins]
left join ( 
    select 
    s.id_products, 
    sum(case when s.type = 'in' then s.quantity else 0 end) as 'in', 
    sum(case when s.type = 'out' then s.quantity else 0 end) as 'out' 
    from stock s 
    group by s.id_products 
) x on x.id_products = p.id_products 
group by id 

---

EDIT: I made a mistake in the query the if statement in the select is just for a flag display. Please replace the following.

if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,

(x.in - x.out, 0) as quantity,

Answer 1

Discarded sums

You calculate these sums:

sum(case when s.type = 'in' then s.quantity else 0 end) as 'in', 
sum(case when s.type = 'out' then s.quantity else 0 end) as 'out' 

but then reduce them to a 0/1 here:

if(coalesce(x.in - x.out, 0) = 0, 0, 1) as quantity,

This means that a sum is not appropriate at all. This should be refactored to an if exists, where the predicate compares s.type to in/out and checks that s.quantity > 0.