Say I have two tables. Table A (material requirements):
product_id | material | required
1 | A | 0.5
1 | B | 0.7
2 | A | 0.2
3 | A | 0.12
And Table B (orders):
order_id | product_id | quantity
1 | 1 | 100
2 | 2 | 10
I want to do a LEFT JOIN* on these two tables (table B being outer), such that the result would be:
order_id | product_id | quantity | material | required
1 | 1 | 100 | A | 0.5
1 | 1 | 100 | B | 0.7
2 | 2 | 10 | A | 0.2
How do I do this? Of course, the end goal is to multiply the required with the quantity
Ideally, there should be an additional table with just basic product information, e.g. something like:
product_id | name | description
*It should be LEFT JOIN, NOT INNER JOIN simply because all orders should be shown, even if the info in product material description is not yet input. An order cannot be 'forgotten' because inner join is used.
Best Answer