MULTIPLE-TABLE SELECT

MULTIPLE-TABLE SELECT

Examples

The Sales by Category query summarizes sales data ($ figures) for all products, sorted by category, using data from three tables (Products, Orders, and Order Details) and the Order Details Extended query (equivalent to a view).

Run the query - notice that there is one row per product. Then switch to SQL view:

SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales

FROM Categories
     INNER JOIN (Products
          INNER JOIN (Orders
               INNER JOIN [Order Details Extended]
               ON Orders.OrderID=[Order Details Extended].OrderID)
          ON Products.ProductID=[Order Details Extended].ProductID)
     ON Categories.CategoryID=Products.CategoryID

WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))

GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName

ORDER BY Categories.CategoryName;

Notes:
The number of joins is equal to the total number of tables (or views) minus one.
A join condition (ON table1.col1 = table2.col2) must be specified for each join.

If the join is in the WHERE clause, the rules are the same - the minimum number of join criteria is equal to the number of tables (or views) minus one.

The GROUP BY clause summarizes data in subsets, in this case giving one row per product. (Topic to be covered in detail in the third class)

The order of clauses in the SQL statement is important: GROUP BY after WHERE (if present), ORDER BY last.

The Invoices query pulls together data from all tables except Categories and Suppliers. Run the query, then go to SQL view:

SELECT

Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,
Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Shippers.CompanyName,
[Order Details].ProductID,
Products.ProductName,
[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,

Orders.Freight

FROM Shippers
     INNER JOIN (Products
               INNER JOIN ((Employees
                         INNER JOIN (Customers
                              INNER JOIN Orders
                              ON Customers.CustomerID=Orders.CustomerID)
                              ON Employees.EmployeeID=Orders.EmployeeID)
                                    INNER JOIN [Order Details]
                                    ON Orders.OrderID=[Order Details].OrderID)
     ON Products.ProductID=[Order Details].ProductID)
     ON Shippers.ShipperID=Orders.ShipVia;

Note: Relationships among the six tables are not linear so it is harder to "see" them in the SQL statement.

Exercises

Create a list of products that shows the category name for each and the contact name of the supplier (77 rows)

Copy and save either sample query above with a different name and rewrite with the join criteria in the WHERE clause (theta style).

Tip: To analyze or troubleshoot a query in the Access query window or in the command line utility in Oracle or MySQL, try breaking the statement as shown in the syntax diagram, with the keywords at the beginning of the lines; or copy and paste to a text editor (e.g., Notepad) and rearrange there.