Using combined queries

Combined queries are a form of advanced querying technique that uses several queries for comparison in order to return a single dataset.

Using combined queries enables us to retrieve results from more than one query into a single table by comparing the rows of each query while a subquery compares a single object result.

Combined queries can use the following operators:

• Union: This will include the rows from both queries (this is also the default operator and is similar to the OR operator between query filters)
• Intersection: This includes the rows that are common to both queries (just like the AND operator between query filters)
• Minus: This includes the rows from the first query minus the rows from the second query
  

When building combined queries, the order, data type, and number of result objects has to be the same; however, there is no limitation to the number of query filters.

Getting ready

We are required to use the Island Resorts Marketing universe this time as our data provider to query customers who used hotel room service and restaurant services.

Since there is no method to perform such a query in a single step, we will create an intersection between two queries.

Note

Using In list in the product query filter will not return the required results as In list uses an OR operator between these list values.

How to do it...

We will create a query with one result object, Customer, and a query filter, Service Equal to Hotel Room. We will click on the Add a combined query button located on the top-left Query Panel toolbar, as shown in the following screenshot:

A second query will be created with the same result object. Note that switching between the queries is done by clicking on one of the gray bars on the bottom-left Combined Query panels, as shown in the following screenshot:

Furthermore, as explained earlier, the default combined query type created is union; by clicking on it, we will change it to intersection, as shown in the following screenshot:

We will add another service query filter to the second query with the value Restaurant. We will click on the Run query button and get the following customer list:

Only customers who used the hotel room and the restaurant are in this list.

Note

When using intersection, all values of the rows returned by the objects have to be the same, including the measure objects. This means an intersection between queries can only compare the same values and can only retrieve distinct values.

Now, we want to edit the query so that it will return those customers who used the hotel room service but didn't use the restaurant service (a minus operation between the two queries). We will go back to Query Panel and click on the minus operator, as shown in the following screenshot:

We will rerun the query and get the list of clients who didn't use a restaurant service but did use the hotel room service, as follows:

How it works...

Combined queries are the way to implement AND/OR/Minus relations between queries. A combined query compares the two queries result sets row by row and returns the relevant results depending on the type of operator between the queries.

Combined queries are great for answering business questions such as:

• Which customers bought a specific product but didn't buy another product (minus operation)?
• Which customers bought a specific product and bought another product as well (an intersection operation)? Since intersection can only compare queries with the same values another good example would be: which customers who ordered a product got it on the same day? (the order date and shipment date will be the same)?
• Which customers have a valid billing date or sale date (union operation)?

  Tip

  Using union operation between queries is useful when we need to compare data between tables that aren't connected in the universe.

There's more...

You might wonder at some point how we can know for sure that the query worked as expected. We can be sure by creating another query that will fetch a specific customer service and checking the results against the original query.

See also

• For how to combine several queries at the report level, see Chapter 8, Merging Data