To continue creating the customerRentals query,
you will add three tables to the SELECT statement. This query will
take input of a particular customer and return the customer's
rented video titles and their due date. To accomplish this, the query
needs to include the CUSTOMERS, RENTALS, and VIDEOS tables.
The CUSTOMERS table contains the name and ID (identification
number) of each person who rents from the video store. The data in
the VIDEOS table includes the ID and title of each video. The data
in the RENTALS table includes the customer ID, video ID, and the due
date for each rented video.
To add tables to the statement:
- In the Data Source Explorer view,
expand VIDEOS Connection > VIDEOS > Schemas > XMLTEST > TABLES. You will see all the tables in the VIDEOS database listed.
There
are three different ways to add a table to the SQL statement. The
next three steps will show you each of these ways.
- First, select the CUSTOMERS table from
the Data Source Explorer view and drag and
drop it to the Tables pane in the SQL
Query Builder. The CUSTOMERS table will
be listed in the Tables pane, and the source
code in the SQL Source pane shows the addition
of the CUSTOMERS table in the SELECT statement.
- You can also add tables by listing them in the SQL statement
in the SQL Source pane of the SQL
Query Builder. In the SQL Source pane of the SQL Query
Builder, type ,xmltest.rentals (including the
leading comma) after XMLTEST.CUSTOMERS, and then
click anywhere outside of the SQL Source pane.
After the change in the SQL statement is parsed and
determined to be valid, the RENTALS table is added in the Tables pane,
and the code in the SQL Source pane is formatted.
- Finally, you can right-click in the Tables pane
and selectAdd Table from the context menu. The Add Table window opens
- Expand the XMLTEST schema, select VIDEOS,
and click OK. The VIDEOS
table is added in the Tables pane, and the
source code in the SQL Source pane shows the
addition of the VIDEOS table in the SELECT statement.
All three tables are now shown in the Tables pane.
Note the corresponding changes to the source code in the SQL
Source pane.