My problem is that I have following tables: packages, orders, order_items
order_items is halding id of package_id, order_id, and credits_number (which I need to retrieve)
order table can be with status = 1 (important for column credits_deducted_total) or 0 (important for credits_reserved_total)
credits_deducted_total and credits_reserved_total are giving me a problems because they should represent the number of deducted or reserved credits per project (which with following query is not the case).
Also 0 should be return as value for each project where I don't have orders with status = 0
Query which I was able to write:
select `packages`.`id` as `id`, `external_id`, `project_name`, `credits_available`, `credits_aq`,
`price`, `currency_code`, `packages`.`status`, `packages`.`updated_at`,
SUM(order_items.credits_number) as credits_deducted_total,
SUM(order_items_reserved.credits_number) as credits_reserved_total from `packages`
left join `order_items` on `order_items`.`package_id` = `packages`.`id`
left join `order_items` as `order_items_reserved` on `order_items_reserved`.`package_id` = `packages`.`id`
left join `orders` on `orders`.`id` = `order_items`.`order_id`
left join `orders` as `orders_reserved` on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
where (`orders`.`status` = 1 or `orders_reserved`.`status` = 0)
group by `packages`.`id`;
Best Answer
Alternatively: