Summary: in this tutorial, we will show you how to compare successive rows within the same table using the self-join technique.
Suppose you have a table called
inventory with the structure defined by the CREATE TABLE statement as follows:
CREATE TABLE inventory(
id INT AUTO_INCREMENT PRIMARY KEY,
counted_date date NOT NULL,
item_no VARCHAR(20) NOT NULL,
qty int(11) NOT NULL
idis an auto-increment column.
counted_dateis the counted date.
item_nois the item code posted to inventory.
qtyis the accumulated on-hand quantity in inventory.
The following is the sample data of the inventory table:
INSERT INTO inventory(counted_date,item_no,qty)
If you want to know how many items received per day for each item, you need to compare the on-hand quantity of a particular day with its previous day.
In other words, in the
inventory table, you need to compare a row with its successive row to find the difference.
In MySQL, you can use self-join technique to compare successive rows as the following query:
(g2.qty - g1.qty) AS receipt_qty
inventory g2 ON g2.id = g1.id + 1
g1.item_no = 'A';
The condition in the INNER JOIN clause g2.id = g1.id + 1 allows you to compare the current row with the next row in the inventory table, of course, with an assumption that there are no gaps in the id columns.
In case you cannot avoid the gap, you can create an additional column e.g.,
seq to maintain the sequences of the rows so that you apply this technique.
In this tutorial, you have learned how to compare successive rows within the same table using the self-join technique.