# Query to retrieve available stock

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,


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.

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