I have the following tables:

Stocks

id	name	amount
1	Pen	35
2	Cd	21
3	Bag	15

StockUnits

id	name	include	stockid
1	onepen	1	1
2	dozen	6	1
3	pocket	24	1
4	onecd	1	2
5	pocket	5	2
6	onebag	1	3

So how do I get a result like the following with SELECT query?

stock	unit	qty
Pen	onepen	5
Pen	dozen	1
Pen	pocket	1
Cd	onecd	1
Cd	pocket	4
Bag	onebag	15

As we see, each stock has some units. Now I want to know how many units do i have according to the amount field, For example:
We have 35 pens which is equal to 1 pocket and 1 dozen and 5 onepen because:

For pocket we have 1 pocket because (each pocket includes 24 pen)
35/24=1
35$=11
For dozen we have 1 dozen because (each dozen includes 6 pen)
11/6=1
11%6=5
For onepen we have 5 onepen because
5 onepen because we don't have any smaller unit

Now we have:
1 pocket
1 dozen
5 onepen

Same calculation for Cd and Bag...

I know to get the desired result a temporary variable should be used to store the result of the MOD and use the variable for the next row but how?

CodePudding user response：

How to store a result of a row in a variable and use it in the next row in MySQL SELECT query?

A pattern for MySQL version 5.x:

SELECT .. , 
       {an expression which uses @variable with the value from previous row} AS calculated_column,
       .. ,
       @variable := {an expression for current row which will be used for next row processing} AS dummy_column,
       ..
FROM {source single table or subquery}
CROSS JOIN ( SELECT @variable := {initial variable value} ) AS init_variable
ORDER BY {ordering expression which provides rows uniqueness}

On MySQL 8 use window function(s).

CodePudding user response：

First of all, your Stocks and StockUnits make no sense, or they are at least bad designed.

You show that you store the id of each stock-row in multiple unit-rows, but it really should be the other way around.

How Stocks table should be:

id	name	qty	unit_id
1	Pen	5	1
2	Pen	1	2
3	Pen	1	3
...

How StockUnits table should be:

id	name	include
1	onepen	1
2	dozen	6
3	pocket	24
...

Example

Once you fix the design mistake, all you would need to do is join to get unit's name, for example in Laravel do something like:

$stockTable = Stock::with('unit')->all();

echo 'stock | unit | qty';
foreach ($stockTable as $item) {
    echo $item->name . ' | ' . $item->unit->name . ' | ' . $item->qty;
}

To get output:

name	unit	qty
Pen	onepen	5
Pen	dozen	1
Pen	packet	1
...