A join operation lets you retrieve data from two or more
tables based on matching column values. The data in the tables is
linked into a single result. Two joins are needed for this query.
The query result needs to include the RENTALS and CUSTOMERS table
entries that have matching CUST_ID column values. The query result
must also include the RENTALS and VIDEOS table entries that have matching
video IDs (VID_ID column values).
- In the Tables pane, right-click the header of the R table,
and then click Create Join on the pop-up menu.
- In the Create Join window under Source,
make the following selections:
- In the Table (alias) list, click RENTALS
(R).
- In the Column list, click CUST_ID.
- Under Target, make the following selections:
- In the Table (alias) list, click CUSTOMERS
(C).
- In the Column list, click CUST_ID.
- Click OK. A join
connector appears between the two columns.
- In the Tables pane, drag your pointer from the VID_ID column
in the R (RENTALS) table to the VID_ID column
in the V (VIDEOS) table.
Look at the SQL Source pane to see the joins in the source
code:
FROM
XMLTEST.CUSTOMERS AS C JOIN XMLTEST.RENTALS AS R ON C.CUST_ID = R.CUST_ID
JOIN XMLTEST.VIDEOS AS V ON R.VID_ID = V.VID_ID
In
the Tables pane, both joins have been created as shown here:

You can change the join type (for example, from the
default inner join to a left, right, or full outer join) in the Tables
pane by right-clicking the connector, clicking Specify
Join Type on the pop-up menu, and then selecting the join
type that you want in the Specify Join window.