MySQL Select problems
- by John Nuñez
Table #1: qa_returns_items
Table #2: qa_returns_residues
I have a long time trying to get this Result:
item_code - item_quantity
2 - 1
3 - 2
IF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 11 THEN
item_quantity = qa_returns_items.item_quantity -
qa_returns_residues.item_quantity
ELSEIF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 12 THEN
item_quantity = qa_returns_items.item_quantity +
qa_returns_residues.item_quantity
ELSE
show diferendes
END IF
I tried this Query:
select SubQueryAlias.item_code,
total_ecuation, SubQueryAlias.item_unitprice,
SubQueryAlias.item_unitprice * total_ecuation as item_subtotal,
item_discount,
(SubQueryAlias.item_unitprice * total_ecuation) - item_discount as item_total
from (
select
ri.item_code
, case status_code
when 11 then ri.item_quantity - rr.item_quantity
when 12 then ri.item_quantity + rr.item_quantity
end as total_ecuation
, rr.item_unitprice
, rr.item_quantity
, rr.item_discount * rr.item_quantity as item_discount
from qa_returns_residues rr
left join qa_returns_items ri
on ri.item_code = rr.item_code
WHERE ri.returnlog_code = 1
) as SubQueryAlias
where total_ecuation > 0 GROUP BY (item_code);
The query returns this result:
item_code - item_quantity
1 - 2
2 - 2
