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,

• Sᴀᴍ Onᴇᴌᴀ: Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see What should I do when someone answers my question? as well as what you may and may not do after receiving answers.

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.

• Matías Cánepa: Hello @Reinderien, I accidentally leave that...but is just for a flag display. I will now add an edit to my post