Database Toolbox

Creating Queries for Results from Multiple Tables

Select multiple tables when creating a query whose results include values from both tables. This is called a join operation in SQL.

This example retrieves sales volumes by product description. The example is very similar to the example in Creating Subqueries for Values from Multiple Tables. The difference is that this example creates a query that uses both tables in order to include the product description rather than the stock number in the results.

The table salesVolume, has sales volumes and a stock number field, but not a product description field. Another table, productTable, has the product description and the stock number, but not sales volumes. Therefore, the query needs to retrieve data from both tables and equate the stock number from productTable with the stock number from the salesVolume table.

1. Select the Data source, for example, dbtoolboxdemo.

The tables in that data source appear in Tables.

2. From Tables, select the tables from which you want to retrieve data. For example, Ctrl-click on productTable and salesVolume to select both tables.

The fields (columns) in those tables appear in Fields. Note that the field names now include the table names. For example, productTable.stockNumber is the field name for the stock number in the product table, and salesVolume.StockNumber is the field name for the stock number in the sales volume table.

3. From Fields, select these fields to be included in the results. For example, Ctrl-click on productTable.productDescription, salesVolume.January, salesVolume.February, and salesVolume.March.
4. In Advanced query options, click Where to make necessary associations between fields in different tables. For example, the where clause equates the productTable.stockNumber with the salesVolume.StockNumber so that the product description is associated with sales volumes in the results.

The WHERE Clauses dialog box appears.

5. In the WHERE Clauses dialog box:
1. Select productTable.stockNumber from Fields.
2. For Condition, select Relation.
3. From the drop-down list to the right of Relation, select =.
4. In the field to the right of the drop-down list, type salesVolume.StockNumber.
5. Click Apply.

The clause appears in the Current clauses area.



6. Click OK.

The WHERE Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box reflects the where clause.

6. Assign a MATLAB workspace variable, for example, P1.
7. Click Execute to run the query.

The results are a 10-by-4 matrix.

8. Type P1 at the prompt in the Command Window to see the results.
   
   

Creating Subqueries for Values from Multiple Tables

Other Features in Advanced Query Options