Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Providing detailed information and counting objects
2. Detail reports
Calculating metrics for multiple business objects
Understanding the difference between various count metrics


Let's get started. The easiest type of report we can create contains a lot of detailed information about one or more business objects. The information we need may be scattered across multiple tables, so we want to bring it all together into a single report. In SQL, we use one or more JOIN clauses to do this. Take a look:

  C.CompanyName AS CustomerCompanyName, 
  E.FirstName AS EmployeeFirstName, 
  E.LastName AS EmployeeLastName,
FROM Orders O
JOIN Employees E
  ON O.EmployeeID = E.EmployeeID
JOIN Customers C
  ON O.CustomerID = C.CustomerID
WHERE O.ShipCountry = 'France';

In the above query, we want to collect various information about orders shipped to France. We want some details about the customers and employees involved in those orders, so we need to join the Orders table with the Employees and Customers tables. Note that each table was given a one-letter alias (such as E for Employees). This reduces the amount of code we have to write and is a common practice when working with long SQL queries.


Your turn now!

Show the following information related to all items with OrderID = 10248: the product name, the unit price (taken from the OrderItems table), the quantity, and the name of the supplier's company (as SupplierName).

Stuck? Here's a hint!

Join the OrderItems and Products tables and the Suppliers and Products tables.