One-to-Many Joins

There is no logical difference between a “many-to-one” join, as described previously, and a “one-to-many” join, except from the point of view of the user. A one-to-many join is sometimes referred to as a master/detail relationship. A single record in the “one” or “master” table is related to one or more records in the “many” or “detail” table. Updatable multiple-table joins are especially useful with these types of one-to-many relationships.

The following query joins the Orders table to the Order Details table in a classic one-to-many relationship:

SELECT 
	Orders.*, 
	[Order Details].*
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID;

Fields in the Orders table focus on the order itself: the customer who placed the order, the employee who took the order, the date the order was taken, and so on. Fields derived from the Order Details table specify the actual items that were ordered: the product’s ID and pricing details. Just as with the many-to-one example shown previously, changes to fields from the “one” table on any given record are automatically made for all other records based on the same value in the “one” table. For example, if the user changes the CustomerID field, which is drawn from the “master” Orders table, for any given record, the change is automatically reflected in all other records for this same order.

Updatable multiple-table joins aren’t limited to a single-level hierarchy. For example, the following query links from the Employees table to the Orders table to the Order Details table:

SELECT 
	Employees.EmployeeID, 
	Employees.LastName, 
	Orders.OrderID, 
	Orders.OrderDate, 
	[Order Details].ProductID
FROM (Employees 
	INNER JOIN Orders 
	ON Employees.EmployeeID = Orders.EmployeeID) 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID;

Fields from all three of these joined tables can be updated in the resulting recordset.