Mysql – joining on 2 tables twice in MYSQL

MySQL

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

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`
    AND `orders`.`status` = 1
left join `orders` as `orders_reserved` 
     on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
    AND `orders_reserved`.`status` = 0
group by `packages`.`id`;

Alternatively:

select `packages`.`id` as `id`, 
       `external_id`, 
       `project_name`, 
       `credits_available`, 
       `credits_aq`, 
       `price`, 
       `currency_code`, 
       `packages`.`status`, 
       `packages`.`updated_at`, 
       SUM(CASE WHEN `orders`.`status` = 1
                THEN order_items.credits_number
                ELSE 0
           END) as credits_deducted_total, 
       SUM(CASE WHEN `orders`.`status` = 0
                THEN order_items.credits_number
                ELSE 0
           END) as credits_reserved_total 
from `packages` 
left join `order_items` 
     on `order_items`.`package_id` = `packages`.`id` 
left join `orders` 
     on `orders`.`id` = `order_items`.`order_id`
group by `packages`.`id`;
Related Question