The query will only return the Cartesian product of the two tables if they
are not explicitly joined, either in a JOIN clause or by a join criterion in
the WHERE clause. The Cartesian product is when every row in one table is
joined to every row in the other, so if there are 100 rows in one, and 1,000
rows in the other the result will be 100,000 rows.
If the tables are INNER JOINed, then for each row in the referenced (parent)
table the number of rows returned will be the number of matching rows in the
referencing (child) table. An INNER JOIN will not return any rows from the
referenced table which do not have at least one matching row in the
referencing table, but you can do so by using an OUTER JOIN (which come in
LEFT and RIGHT flavours), so if your tables are Orders and LineItems say, a
query which returns all orders, along with data from the matching rows from
the LineItems table, and NULLs in the LineItems columns where there is no
match might be something like this:
SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID;
If you wanted zeros returned in place of the NULLs in the case of Orders
with no matching Lineitem records you can use the NZ function:
SELECT CustomerID, OrderDate, NZ(ItemID,0) AS item_ID,
NZ(UnitPrice,0) AS Unit_Price, NZ(Quantity,0) As Quantity_Ordered
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID;
The SQL COALESCE function works rather differently from the VBA NZ function.
The former scans a value list and returns the first NOT NULL value. As far
as I know its not supported by Jet SQL. The NZ function returns the value of
the first argument if NOT NULL, the value of the second argument if the first
is NULL. THE COALESCE function can be used in this way of course in standard
SQL, where COALESCE(ItemID, 0) would be the equivalent of NZ(ItemID,0) above.
One thing to note about OUTER JOINs is that you can not restrict the result
set on the basis of values in the table on the outer side of the join, so you
could not have:
SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE UnitPrice > 100;
But you could have:
SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE YEAR(OrderDate) = 2008;
The former would in fact return exactly the same results as an INNER JOIN.
In a case like the above, where multiple rows per order are, quite
correctly, returned by the query this would normally be where the query is
being used as the basis for a report. The report can then be grouped by
OrderID and the order data put in a group header with the line items in the
detail section, thus avoiding any duplication in the final presentation of
the data. Ina form the more common approach would be to have a form based on
Orders and a subform based on LineItems rather than using a query which joins
the tables as the basis for a single form.
Ken Sheridan
Stafford, England