I have a 3 table setup ( Branches, Product, and Waste ). The goal is to get a result including all enabled branches, with all enabled products and their waste for the entire week
Table: Branches
| id | Branch Name | Is Enabled |
|---|---|---|
| 1 | Big Branch | 1 |
| 2 | Medium Branch | 0 |
| 3 | Small Branch | 1 |
Table: Waste
| id | Branch ID | Product ID | week number | Mon | Tues | Wed | Thu | Fri | Sat | Sun |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 30 | 10 | 0 | 5 | 0 | 0 | 0 | 0 |
Table: Product
| id | Name | Is Enabled |
|---|---|---|
| 1 | Bread | 1 |
| 2 | Cream | 1 |
| 3 | Rice | 1 |
| 4 | Milk | 0 |
Ideal Result
| waste.id | branch id | branches.name | week number | product.id | product.name | Mon | Tues | Wed | Thu | Fri | Sat | Sun |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Big Branch | 30 | 1 | Bread | 10 | 0 | 5 | 0 | 0 | 0 | 0 |
| null | 1 | Big Branch | null | 2 | Cream | null | null | null | null | null | null | null |
| null | 1 | Big Branch | null | 3 | Rice | null | null | null | null | null | null | null |
| null | 3 | Small Branch | null | 1 | Bread | null | null | null | null | null | null | null |
| null | 3 | Small Branch | null | 2 | Cream | null | null | null | null | null | null | null |
| null | 3 | Small Branch | null | 3 | Rice | null | null | null | null | null | null | null |
SQL Query Attempt
SELECT waste.id, product.id, product.name, product.is_enabled waste.product_id, waste.id, week_number, waste.branch_id, branches.id, branches.branch_name, branches.is_enabled,
mon, tue, wed, thu, fri, sat, sun
FROM `product`
LEFT JOIN waste
ON product.id = waste.product_id
LEFT JOIN branches;
What kind of join should be used to achieve this result?
Frist CROSS JOIN two master tables (branch and product) and retrieve only enabled branch and product and use this result as subquery. Then left join waste table with subquery for getting desired result.
-- MySQL
SELECT w.id wast_id, t.branch_id
, t.branch_name, w.week_number
, t.product_id, t.product_name
, w.mon, w.tues, w.wed, w.thu
, w.fri, w.sat, w.sun
FROM (select b.id branch_id
, b.branch_name
, p.id product_id
, p.name product_name
from Branches b
cross join Product p
where b.is_enabled = 1
and p.is_enabled = 1) t
left join Waste w
on t.branch_id = w.branch_id
and t.product_id = w.product_id
order by t.branch_id, t.product_id;
Please check this url https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4a25c2ce1f1af6729a0cfe34d8998eb6