MYSQL join 3 tables but include all rows on 2 tables

Question

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?

Answer 1

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