Select Records from a Nested Join Sample
File: ...\Samples\Data\Nested.qpr
The query, NESTED, in the Solution project combines information from the customer, orders, and orditems tables using two inner join conditions. One join condition is between customer and orders; the other between orders and orditems. Each result record has fields for the cust_id and company from the customers table, order_id from the orders table, and line_no from the orditems table as specified in the SELECT clause of the SELECT-SQL statement.
SELECT Customer.cust_id, Customer.company, Orders.order_id, Orditems.line_no;
FROM testdata!customer INNER JOIN testdata!orders;
INNER JOIN testdata!orditems ;
ON Orders.order_id = Orditems.order_id ;
ON Customer.cust_id = Orders.cust_id
The order in which the tables are joined determines the order in which the join conditions are evaluated. In this query, the join between orders and orditems is evaluated first and produces a subset of records that meets the join condition. This subset of records is matched to the records in the customer table and produces the final results set.
You can change the results of the query by specifying filters, a sort order, group, or other miscellaneous options for the query.