Filter
Top Products
manipulated.
In this example, the correlation names "T1" and "T2" are specified for the ORDER table and the STOCK table,
respectively. One of these correlation names is used as the column name qualifier. The correlation name is valid only
in the specified SQL statement. If a correlation name is specified, the correlation name must be used in the column
name qualifier. The original table name cannot be used as the column name qualifier.
5.4 Joining a Table to Itself and Manipulating Data
This manual has already explained comparing a value in one column with a value in another column and performing
operations on values in rows of columns in tables. Comparing and manipulating values in different rows of the same
table has not so far been possible. This section explains how to manipulate data in different rows by joining a table to
itself as if joining it to a different table.
To join a table to itself, specify the table name more than once in the FROM clause. Then, specify a correlation name
in the SQL statement for each table so that the tables can be distinguished.
Comparing different rows
The following is an example of comparing values from different rows of the same table:
Example 1:
In this example, pairs of ITMNO for which PRODUCT is identical are obtained from the STOCK table.
The columns to be fetched are the ITMNO pair and PRODUCT. To perform this data manipulation, the
STOCK table is specified twice in the FROM clause and a table is derived that includes two identical
columns. To distinguish identical columns, add the correlation name T1 to one column and T2 to the
other.
64