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.>