Database Toolbox

Subquery

A subquery restricts query results by using values found in other tables. For example, use a subquery if you want to know sales volumes for the building blocks product, but the sales volumes and product descriptions are not in the same table. The subquery uses a field common to both tables to relate the tables.

In Advanced query options, click Where; the Where Clauses dialog box appears. In the Where Clauses dialog box, click Subquery; the Subquery dialog box appears.

1. From Tables, select the table that contains the association you need for the query results.
2. From Fields, select the field that is common to both tables.
3. In the Subquery Where Clauses area, specify the associations between the fields that are in different tables, and click Apply.

The subquery appears in the Current subquery Where clauses area and in the SQL Subquery Statement area.

4. You can make changes to subquery clauses.
• To edit a subquery, select the clause from the Current subquery Where clauses area and click Edit. Make changes to the subquery fields and click Apply.
• To delete a subquery, select the clause from the Current subquery Where clauses area and click Delete. Use Ctrl-click or Shift-click to select multiple clauses to delete.
5. Click OK to close the Subquery dialog box, which updates the Where Clauses dialog box with the subquery you created in the Subquery dialog box. Or click Cancel to close the Subquery dialog box without updating the Where Clauses dialog box.
6. Click OK to close the Where Clauses dialog box.

The SQL statement in the Visual Query Builder dialog box includes the subquery.

Subquery Where Clauses

In the Subquery Where clauses area of the Subquery dialog box, specify the associations between fields that are in different tables.

1. From Fields, select the entry whose value you want to specify.
2. Specify the condition for the selected field - for more information, see Condition as described for where clauses.
3. Select an operator to add another condition - for more information, see Operator as described for where clauses.
4. To create the clause, click Apply.

The subquery appears in the Current subquery Where clauses area and in the SQL subquery statement area.

Example Using Subquery

This example retrieves sales volumes for the product whose description is Building Blocks. The salesVolume table 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 the sales volumes. The subquery looks at productTable to get the stock number for the product whose description is Building Blocks. The main query gets the sales volume values for that stock number from the salesVolume table.

In the Subquery dialog box:

1. From Tables, select productTable.
2. From Fields, select stockNumber.

This begins creating the subquery to retrieve stockNumber from productTable; it appears in the SQL subquery statement area.

3. Specify the condition that instructs the query to select the products whose productDescription is 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.



4. Click OK.

The Subquery dialog box closes.

5. In the Where Clauses dialog box, click Apply.

This updates the Current clauses area, using the subquery criteria specified in steps 1 through 5.

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

7. In the Visual Query Builder dialog box:
1. Create the main query that selects sales volumes from the salesVolume table.
2. Assign the MATLAB workspace variable and click Execute to run the query.

The results show sales volumes for the item whose product description is Building Blocks, although the product description itself is not included in the results.

You can select multiple tables in the Visual Query Builder dialog box and create a Where clause to perform the same query. That method also allows you to include the product description in the results. See Example Creating Queries for Results from Multiple Tables for more information.

See Also

• Where Clauses Dialog Box
• Advanced Query Options
• Visual Query Builder Dialog Box

Current clauses

Group By