Query to retrieve available stock
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 &gt; 0.