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,