given 3 tables:
table_1 has factory, material, date, quantity fields indicating what factory to can produce deliver which material by the specific date.
table_2 has company, material, date, quantity fields indicating what company wants to have some amount of material by the specific date.
table_3 has company, factory, material fields indicating what material a company can buy from which factory.
Write a sql to output a table with company, factory, material, quantity, date fields, for each row, it is the real amount that a factory can deliver to a company
on that date, the quantity might be zero.
If there are more demand than production, a factory takes earlier order or the biggest amount order if both orders have the same demanding date.
sample input
table_1
factory material quantity date
f1 a 100 2010-10-10
f1 b 200 2010-10-10
f1 a 500 2011-11-11
f2 a 300 2010-10-10
table_2
company material quantity date
c1 a 100 2010-10-12
c2 b 200 2010-10-10
c1 a 500 2011-11-11
c3 a 300 2010-10-10
table 3
company factory material
c1 f1 a
c1 f2 a
c2 f1 b
c3 f2 a
- krithi August 26, 2019