Adding joins, a query condition, and a GROUP BY clause

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).

1. In the Tables pane, right-click the header of the R table, and then click Create Join on the pop-up menu.
2. In the Create Join window under Source, make the following selections:
   1. In the Table (alias) list, click RENTALS (R).
   2. In the Column list, click CUST_ID.
3. Under Target, make the following selections:
   1. In the Table (alias) list, click CUSTOMERS (C).
   2. In the Column list, click CUST_ID.
4. Click OK. A join connector appears between the two columns.
5. 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.

   FROM
      XMLTEST.VIDEOS AS V JOIN XMLTEST.RENTALS AS R JOIN XMLTEST.CUSTOMERS AS C
      ON R.CUST_ID = C.CUST_ID ON V.VID_ID = R.VID_ID

Next, the query needs a query condition so that the query extracts only result rows that have the customer name that you want. You add conditions to the query by using the Conditions page in the Design pane.

1. Click the Conditions tab to see the Conditions page.
2. In the first row, double-click the cell in the Column column and then click C.NAME in the list.
3. In the same row, double-click the cell in the Operator column and then click the = operator.
4. In that row, double-click the cell in the Value column and then enter :custName. A colon followed by a variable name is the SQL syntax for a variable that will be substituted with a value when you run the query. You will see how this works when you run the SQL query.

You will group the query by the day of the week, then by title, and then by customer name.

1. In the Design pane, click the Groups tab.
2. Add the DAYOFWEEK function as a result column.
   1. In the Column table, double-click the first row, click Build Expression in the list, and then press Enter.
   2. On the Expression Types page of the wizard, click Function, and then click Next. The Function Expression Builder page opens.
   3. In the Select a function category list, click Date and time.
   4. In the Select a function list, click DAYOFWEEK.
   5. In the Select a function signature list, click DAYOFWEEK(DATE) --> INTEGER. The function signature shows that the function requires one argument.
   6. In the Value column of the argument table, click the cell, click R.DATE in the list, and then press Enter.
   7. Click Finish. The DAYOFWEEK function is shown in the first cell of the Column table.
3. In the second row of the Column table, select the V.TITLE column from the list, and then in the third row, select C.NAME. The Groups page now looks like the following image: