Query with a subquery

Sometimes the query conditions are so complex that you can’t express them using plain WHERE clause with AND, OR and NOT. In this case, you need to use subqueries.

For instance, let’s calculate total lifetime count of paid orders for those countries that have at least one paid order made in September of 2012.

Expected SQL:

SELECT Cust.Country AS [Customer Country], COUNT(Ord.OrderID) AS [Order OrderID Count] 
FROM (Customers AS Cust
  RIGHT OUTER JOIN Orders AS Ord ON (Ord.CustomerID = Cust.CustomerID ))
WHERE
(
  Cust.Country IN
  (SELECT CustSQ1.Country 
  FROM (Customers AS CustSQ1
  RIGHT OUTER JOIN Orders AS OrdSQ1 ON (OrdSQ1.CustomerID = CustSQ1.CustomerID ))
  WHERE
  (
  OrdSQ1.OrderDate BETWEEN #2012-09-01# AND #2012-09-30# 
  AND OrdSQ1.Paid = true
  )
  ) 
  AND Ord.Paid = true
)
GROUP BY Cust.Country

Here “those countries that have at least one paid order made in September of 2012” condition is implemented as sub-query.

First, add columns - click on “[Add column]” link, select “Customer | Country” menu item, then - [Add column] -> “Order | OrderID”.
Now aggregate the OrderID column - click the Function button on the right side of the column and select “Count” menu item.

Add a condition with a subquery. Click “Add Condition”, select “Customer | Country” from the menu. Then change the operator - click on “is equal to” link and select “in subquery”. Then click “Edit subquery” link.

The sub-query editing dialog appears.

You need to select the single attribute of the same type as our “Customer | Country” attribute.

The “Conditions” area have the same user interface as regular EasyQuery. Here you add the following conditions:

“Order | Date” -> select “custom period” operator -> set start and end date via date picker

“Order | Paid” -> select “is true”.

Click “OK” button to submit the sub-query.

At last, add “Order | Paid is true” condition to the main query.