Having these 2 tables (from inventory functionalities)
-- ins table
+------+-------------+-------------+
| id | direction | quantity |
+------+-------------+-------------+
| 1 | in | 5 |
| 2 | in | 3 |
+------+-------------+-------------+
-- outs table
+------+-------------+-------------+
| id | direction | quantity |
+------+-------------+-------------+
| 1 | out | 4 |
| 2 | out | 1 |
| 3 | out | 2 |
| 4 | out | 1 |
+------+-------------+-------------+
How can I join rows from outs
table to a row from ins
table that has quantity covers/equals to the quantities of the outs
rows that joined it, in other words how to get a result like this ?
-- result
+------+-------------+-------------+------+-------------+-------------+
| id | direction | quantity | id | direction | quantity |
+------+-------------+-------------+------+-------------+-------------+
| 1 | out | 4 | 1 | in | 5 |
| 2 | out | 1 | 1 | in | 5 |
| 3 | out | 2 | 2 | in | 3 |
| 4 | out | 1 | 2 | in | 3 |
+------+-------------+-------------+------+-------------+-------------+
as you can see rows 1,2 from outs
table is taken from/ joined to row 1 from ins
table and rows 3,4 from outs
table is taken from/ joined to row 2 from ins
table
NOTE: the quantities in the 2 tables are guaranteed to be sealed (a row from ins
table is always has quantity that is exactly equal to 1 or more quantities of rows from table outs
)
I wish I can just do something like this
-- sedu SQL
SELECT
whatever
FROM
outs left join
ins on outs.quantity <= (ins.quantity - previously joined outs.quantities);