MYSQL join 3 tables but include all rows on 2 tables

user_4794329 0
Viewed 45

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?

mysql
1 Answers

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

user_163646250
Related