Subqueries in GXquery

Let’s suppose that we want to know the other destinations visited by those customers who have traveled to Brazil. To obtain this information, we need to know which customers have traveled to Brazil and we also need the list of destinations visited by customers in order to keep those who meet the condition of having traveled to Brazil.

This is the type of cases where subqueries can be helpful because they are used to provide a list of values to be used in a filter. That is to say, it’s a query inserted into another query in the WHERE clause.

The subquery (internal query) obtains a value or list of values that is used by the query. Using a nested subquery is the equivalent to running two sequential queries and using the result of the internal query as a search value in the external query (main query).

Subqueries are used when we can’t obtain information with simple filters, which are easier to use.

Syntax

Att in [Att Where (condition)]

Example

We will create the query for the example above. We have a pivot table where we insert the fields in the areas that will be indicated. We will try simple filters first, because maybe they will be enough.

Case 1

Customer  name Row
Arrival Country Name Row

 

If we run the query we will obtain the entire list of customers with all their destinations, so this option doesn't solve the problem.

Case 2

PassengerFullName Row
ArrivalCountryName Row
ArrivalCountryName = “Brazil” Filter

 

If we run this query, it will return only those customers who have traveled to Brazil, but it won’t include the other destinations they have visited. Therefore, this solution is not correct either.

Case 3

PassengerFullName Row
ArrivalCountryName Row
PassengerFullName IN [PassengerFullName where (ArrivalCountryName = “Brazil”)] Filter

 

If we run this query, it will return the destinations of all those customers who have ever traveled to Brazil. Therefore, this is the correct solution.

To insert the subquery, click on the green button in the filter area and type the code inside the expression editor as shown below.

GXquery4 - Subquery code

See also