This post is a continuation of the post called SQL Server Logical Query. It is based on the example in the book called T-SQL Querying by Itzik Ben-Gan published by Microsoft Press in 2015.

One-To-Many

This example uses two tables that have a one-to-many relationship. We have a Customers table and an Orders table. Customers can have none, one or many orders. The tables are intentionally simple. Running a SELECT query on each produces the following results.

In MS Access

SELECT Customers.custid, Count(Orders.orderid) AS numorders
FROM Customers LEFT JOIN Orders ON Customers.custid = Orders.custid
WHERE (((Customers.city)="Madrid"))
GROUP BY Customers.custid
HAVING (((Count(Orders.orderid))<3))
ORDER BY Count(Orders.orderid);