Database Toolbox

Creating Subqueries for Values from Multiple Tables

Use the Where feature in Advanced query options to specify a subquery, which further limits a query by using values found in other tables. This example uses basic.qry (see Building, Running, and Saving a Query).

This example retrieves sales volumes for the product whose description is Building Blocks. The table used for basic.qry, salesVolume, has sales volumes and a stock number field, but not a product description field. Another table, productTable, has the product description and stock number, but not the sales volumes. Therefore, the query needs to look at productTable to get the stock number for the product whose description is Building Blocks, and then has to look at the salesVolume table to get the sales volume values for that stock number.

1. Load basic.qry. For instructions, see Using a Saved Query.

This creates a query that retrieves the values for January, February, and March sales for all stock numbers.

2. In Advanced query options, click Where.

The Where Clauses dialog box appears.

3. Click Subquery.

The Subquery dialog box appears.

4. From Tables, select the table that contains the values you want to associate. In this example, select productTable, which contains the association between the stock number and the product description.

The fields in that table appear.

5. From Fields, select the field that is common to this table and the table from which you are retrieving results (the table you selected in the Visual Query Builder dialog box). In this example, select stockNumber.

This begins creating the SQL subquery statement to retrieve the stock number from productTable.

6. Create the condition that limits the query. In this example, limit the query to those product descriptions that are Building Blocks.
1. In Subquery WHERE clauses, select productDescription 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 'Building Blocks' (include the single quotation marks).
5. Click Apply.

The clause appears in the Current subquery WHERE clauses area and updates the SQL subquery statement.



7. In the Subquery dialog box, click OK.

The Subquery dialog box closes.

8. In the WHERE Clauses dialog box, click Apply.

This updates the Current clauses area using the subquery criteria specified in steps 2 through 7.

9. In the WHERE Clauses dialog box, click OK.

This closes the WHERE Clauses dialog box and updates the SQL statement in the Visual Query Builder dialog box.

10. In the Visual Query Builder dialog box, assign a MATLAB workspace variable, for example, C.
11. Click Execute.

The results are a 1-by-4 matrix.

12. Type C at the prompt in the MATLAB Command Window to see the results.
    
    
13. The results are for item 400345, which has the product description Building Blocks, although that is not evident from the results. To verify that the product description is actually Building Blocks, run this simple query:
1. Select dbtoolboxdemo as the Data source.
2. Select productTable from Tables.
3. Select stockNumber and productDescription from Fields.
4. Assign a MATLAB workspace variable, for example, P.
5. Click Execute.
6. Type P at the prompt in the Command Window to view the results.
   
   

The results show that item 400345 has the product description 'Building Blocks'. Creating Queries for Results from Multiple Tables creates a query that includes the product description in the results.

Presenting Results in Specified Order

Creating Queries for Results from Multiple Tables