Example 2: Querying multiple files with JOIN BY

This query finds the suppliers and prices for a given product. Multiple files are joined so they can be queried as if they were one file. This is necessary because the GROCERY library stores data in separate files linked by common fields, rather than using one large file: The PRODUCT and SUPPROD files share the ProdID field, and the SUPPROD and SUPPLIER files share the SupID field.

With JOIN BY, the relationships between files become part of the query. Without joining the files, you would need three queries to get the same result: one for product ID, one for product cost, and one for supplier name.

To find the suppliers and prices for Dave’s Mango Passion soda:

Specify the GROCERY/PRODUCT, GROCERY/SUPPROD, and GROCERY/SUPPLIER files in the Lib/File(Member) field on the main file transfer screen, then open the From AS/400 - Options dialog box.
To transfer only the product name, product cost, and supplier name fields, type the following string in the Select field on the Overview page:
T1.ProdName, T2.ProdCost, T3.SupName
To join records in the three files by the product ID and supplier ID, type the following string in the Join By field:
T1.PRODID = T2.PRODID AND T2.SUPID = T3.SUPID
To search all records with the string “Mango Passion”, type the following string in the Where field:
T1.PRODNAME = 'Dave’s Mango Passion soda'

Click Apply to update the query fields.

The Query box at the top of the Overview page displays the complete query statement:

SELECT T1.ProdName, T2.Prodcost, T3.SupName
FROM grocery/product, grocery/supprod, grocery/supplier
WHERE (T1.PRODID=T2.PRODID AND T2.SUPID=T3.SUPID) 
AND (T1.PRODNAME=’Dave’s Mango Passion soda’)

The resulting records are:

	ProdCost	SupName
Dave’s Mango Passion soda	3.00	Kolb Bros.
Dave’s Mango Passion soda	2.90	D & C Distributors
Dave’s Mango Passion soda	2.85	Silves Wholesale