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:
T1.ProdName, T2.ProdCost, T3.SupName
T1.PRODID = T2.PRODID AND T2.SUPID = T3.SUPID
T1.PRODNAME = 'Dave’s Mango Passion soda'
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:
|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|