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

Selecting Data From More Than One Table

HP NonStop SQL/MX Quick Start—523724-002

5-2

Qualifying Ambiguous Column Names

Because the PARTNUM column has the same name in both the PARTS and ODETAIL

tables, always qualify PARTNUM with a table name or a correlation name. For

example, PARTS.PARTNUM uses the table name.

Example

Compare the quantity available of part number 5100 with the quantity ordered. Specify

in the WHERE clause that you want rows from the PARTS table joined to rows in the

ODETAIL table that have the same part number value in the PARTNUM column:

SELECT PARTS.PARTNUM, QTY_AVAILABLE, QTY_ORDERED

FROM PARTS, ODETAIL

WHERE PARTS.PARTNUM = ODETAIL.PARTNUM

AND PARTS.PARTNUM = 5100;

The selected rows are:

Part/Num Qty/Avail Qty/Ord

-------- ------------ -----------

5100 3237 10

5100 3237 5

5100 3237 60

5100 3237 12

--- 4 row(s) selected.

Tip

Only rows with PARTNUM 5100 are joined. The PARTS table contains only one row

with PARTNUM 5100. This row is combined with each of the four rows in the ODETAIL

table. The PARTNUM and QTY_AVAILABLE information is the same. You can improve

this SELECT statement by using a GROUP BY clause and the SUM function.