Access Structured Query Language (SQL) - Access Database Design and Programming - page 90

Here ColumnsRequested is a list of columns requested by the statement, and
TablesClause is a join of tables. Let us refer to a table mentioned in TablesClause as a
return table if at least one of its columns is mentioned in ColumnsRequested . Thus, in
the statement:

SELECT DISTINCTROW PubName
FROM PUBLISHERS INNER JOIN BOOKS
ON PUBLISHERS.PubID = BOOKS.PubID;

PUBLISHERS is a return table, but BOOKS is not. Here is how DISTINCTROW works:

1. Form the join(s) described in TablesClause.
2. Project the resulting table onto all of the columns from all return tables (not just
the columns requested). Put another way, remove all columns that are not part of a
return table.
3. Remove all duplicate rows, where two rows are considered duplicates if they are
composed of the same rows from each result table. It is not the values that are
compared, but the actual rows. It is necessary to add this because two different
rows may have identical values in an Access table.

Let us illustrate with a simple example.

Consider the following tables, named Temp1, Temp2, and Temp3, respectively:

A 1 A 2

a1 x

a2 l ink

a3 l ink

B 1 B 2 B 3

b1 y z

b2 lin k link2

C 1 C 2 C 3

c1 t link2

c2 v link2

c3 a x

The statement:

SELECT *
FROM
(Temp1 INNER JOIN Temp2 ON Temp1.A2 = Temp2.B2)
INNER JOIN Temp3 ON Temp2.B3 = Temp3.C3;

gives the result table tblALL: