SQL/MX Quick Start (G06.24+, H06.03+)

Selecting Data From More Than One Table
HP NonStop SQL/MX Quick Start—523724-002
5-3
Using Correlation Names
Using Correlation Names
You can define explicit correlation names for the PARTS and ODETAIL tables. For
example, use the letter P for PARTS and the letter O for ODETAIL. Using correlation
names shortens the length of the text you type.
Example
Compute the sum of the quantity ordered for each part number in the PARTS table.
Group by the columns in the PARTS table:
SELECT P.PARTNUM, QTY_AVAILABLE, SUM(QTY_ORDERED)
FROM PARTS P, ODETAIL O
WHERE P.PARTNUM = O.PARTNUM
GROUP BY P.PARTNUM, QTY_AVAILABLE;
Some of the selected rows are:
Part/Num Qty/Avail (EXPR)
-------- ------------ -------------------
244 4426 47
2001 2100 155
2403 3312 64
5100 3237 87
... ... ...
--- 27 row(s) selected.
Tip
Each group consists of rows with equal values for both part number and quantity
available.
Example
As a sales representative, you can verify the unit price you charged a particular
customer for a particular part by combining data from the ORDERS and ODETAIL
tables. These tables both contain an ORDERNUM column:
SELECT CUSTNUM, PARTNUM, UNIT_PRICE
FROM ORDERS ORD, ODETAIL ODE
WHERE ORD.ORDERNUM = ODE.ORDERNUM
AND CUSTNUM = 5635 AND PARTNUM = 5103;
The selected row is:
Cust/Num Part/Num Unit/Price
-------- -------- ------------
5635 5103 400.00
--- 1 row(s) selected.