2

Having these 2 tables (from inventory functionalities)

SQL Fiddle

-- 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);
Accountant م
  • 6,975
  • 3
  • 41
  • 61
  • 1
    What version are you running? "Windowing" functions (for cumulative sums) are available in MariaDB 10.1(?) and MySQL 8.0. – Rick James Apr 09 '18 at 14:17
  • @RickJames it's `5.6.38` but I'm ready to update anything so my application can scale – Accountant م Apr 09 '18 at 19:49
  • @RickJames If you have time, can you please check [this question](https://stackoverflow.com/questions/49751952/how-do-i-not-normalize-continuous-data-ints-floats-datetime) about your rule of not normalizing continuous data ? – Accountant م Apr 10 '18 at 11:06

2 Answers2

2

This is painful to do in MySQL for a couple of reasons. First, MySQL doesn't have very good support for cumulative sums, which is what you want to compare.

And second, your result set is a little bit weak. It makes more sense to show all the ins records that contribute to each outs record, not just one of them.

For this purpose, you can use a join on cumulative sums, which looks like this:

select o.*, (o.to_quantity  - o.quantity) as from_quantity,
       i.*
from (select o.*,
             (select sum(o2.quantity)
              from outs o2
              where o2.id <= o.id
             ) as to_quantity
      from outs o
     ) o join
     (select i.*,
             (select sum(i2.quantity)
              from ins i2
              where i2.id <= i.id
             ) as to_quantity
      from ins i
     ) i
     on (o.to_quantity  - o.quantity) < i.to_quantity and
        o.to_quantity > (i.to_quantity  - i.quantity)

Here is the SQL Fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much Mr. Gordon for that unbelievable amount of help, experience, ideas, smart ways of thinking you give to us on SO community. – Accountant م Apr 09 '18 at 11:40
  • Regarding your valued note about my result set, the data of the quantities in the database is guaranteed to be 1 or more `out` row is always taken from **only 1** `in` row (*no* many `ins` contribute to 1 `out`), as I will use this query to calculate the [Cost of goods sold](https://en.wikipedia.org/wiki/Cost_of_goods_sold) so the query answers the question "which `in` row will be taken from for each `out` row ?" – Accountant م Apr 09 '18 at 11:41
1

Subquery with correlation approach might also useful

select t.id, t.direction, t.quantity, i.id, i.direction, i.quantity  
from (
      select id, direction, quantity, 
             quantity + coalesce((select quantity from outs where id < o.id order by id desc limit 1),
                      (select quantity from outs where id > o.id order by id limit 1)) Qty
      from outs o
)t inner join ins i on i.quantity = t.Qty
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • Thank you very much for your time and help, I appreciate your help, but I think your solutions is adjusted for the sample data-set in the question as it accumulate the quantity of the 1 previous `out` row, and [it will not work if the data-set is changed](http://sqlfiddle.com/#!9/330413/1). Thank you for introducing `coalesce` function as I wasn't aware of it before. – Accountant م Apr 09 '18 at 11:40