Database Toolbox Previous page   Next Page

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 is referred to as nested SQL. With the VQB, you can include only one subquery; use Database Toolbox functions to use multiple subqueries.

This example uses basic.qry (see Creating and Running a Query to Import Data). It 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.
  1. This creates a query that retrieves the values for January, February, and March sales for all stock numbers.

  1. Set Preferences. For this example, set Data return format to cellarray and Read NULL numbers as to NaN.
  2. In Advanced query options, click Where.
  1. The Where Clauses dialog box appears.

  1. Click Subquery.
  1. The Subquery dialog box appears.

  1. 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.
  1. The fields in that table appear.

  1. 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.
  1. This begins creating the SQL subquery statement to retrieve the stock number from productTable.

  1. 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 to denote it is a string).
    5. Click Apply.
  2. In the Subquery dialog box, click OK.
  1. The Subquery dialog box closes.

  1. In the Where Clauses dialog box, click Apply.
  1. This updates the Current clauses area using the subquery criteria specified in steps 3 through 8.

  1. In the Where Clauses dialog box, click OK.
  1. This closes the Where Clauses dialog box and updates the SQL statement in the Visual Query Builder dialog box.

  1. In the Visual Query Builder dialog box, assign a MATLAB workspace variable, for example, C.
  2. Click Execute.
  1. The results are a 1-by-4 matrix.

  1. Type C at the prompt in the Command Window to see the results.

  2. 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. This clears the VQB selections made during a previous query.
    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.


Previous page  Presenting Results in Specified Order Creating Queries for Results from Multiple Tables Next page

© 1994-2005 The MathWorks, Inc.