Updatable One-to-Many Outer Joins

As discussed earlier in this chapter, an outer join selects all records from one table in a multiple-table join, while only selecting records with matching values from another table. For records in which there is no match in one table, artificial Null field values are supplied. Microsoft Jet allows you to “fill in the blanks” in these artificial Null records.

Consider the following outer join and a portion of its results:

SELECT 
	Customers.CompanyName,
	Customers.CustomerID,
	Orders.CustomerID,
	Orders.OrderID
FROM Customers 
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

In this example, the Customers table is outer joined with the Orders table to show all customers regardless of whether they have placed an order. The first three customers listed have not placed orders. The “fill in the blank with key propagation” rule states that the user can add an order for this customer by filling in the Orders.OrderDate field (and any other order information except the customer’s ID). The value in Customers.CustomerID is automatically propagated into Orders.CustomerID. In this update scenario, the child key (Orders.CustomerID) is read-only. The act of “filling in the blank” associates the new record in the Orders table with the customer.