Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Implementing Subqueries

Objective

After completing this lesson, you will be able to create a subquery

Subqueries

There are various scenarios when subqueries are useful.

Scenarios for subquery use:

When the results of the main query are dependent upon the results of the inner query.
When the value of the operand is unknown.
When the query filter for the report involves a value that changes over time.

Like combined queries, subqueries are always built in the Query Panel .

When creating a subquery, process the inner query first so that the result set can be passed to the main query.

You construct a subquery by placing a query filter on one of the objects in the main query and then using the operand for that query filter to launch the subquery. The operator you include in the query filter determines the relationship between the data sets returned by the inner and outer queries.

Subquery Creation

Watch this video to learn about the subqueries.

To resolve this problem, use a subquery. Begin by creating a query using the Store name and Sales revenue objects. Then choose Add a subquery.

By default, the Sales revenue object appears in the subquery definition in the Query Filters panel. To display the results for all stores with sales revenue greater than or equal to the sales revenue of the Chicago 33rd location in your report, change the operand in the subquery to Greater than or Equal to. Then drag the Store name dimension to just below the subquery filter definition to complete the subquery.

Use the following syntax:

Store name Equal to e-Fashion Chicago 33rd

Subquery: See the text before and after the image for more details.

When you run the query, the report returns the results for the Chicago 33rd store, as well as results for all the other stores with equal or greater sales revenue than the revenue of the Chicago 33rd store.

Procedure: To Build a Subquery

Follow these steps to build a subquery:

Include the objects that you want to appear in the query to the Result Objects panel.
Select the object that you want to filter with a subquery.
In the Query Filters panel, choose Add a Subquery.
The Subquery outline appears in the Query Filters panel.
To add a Where condition to the subquery, move an object to the To Filter the Query area.
Select an operator and values to filter the object in the Where condition.
To use a standard query filter as a Where condition in a subquery, move the existing filter or subquery to the To Filter the Query area
To copy rather than move the existing filter to the Where condition, use the Ctrl key while you move the object. In this way, the existing filter remains in its place and also becomes part of the Where condition of the subquery.
To add an additional subquery to the query filter, choose Subquery.
To nest a subquery within another subquery, move an existing subquery to the Query Filter panel. In this way, the subquery becomes a part of the Where condition of the outer subquery.
To change the relationship between the queries from And to Or, choose the And operator.

Implement Subqueries

ExerciseStart Exercise

Log in to track your progress & complete quizzes