Self-joins

[Alisa]

I have a table where we need to select unique records based on three fields
(i.e., those three fields are acting as a primary key). They are not true
duplicates because those are the only three fields that match. In any case,
I have all of the record pairs in a separate table, and I need to mark each
record of the pair as kept or removed based on values in other fields in the
table. For instance, if field5 equals yes for both duplicate records in the
pair, then I want to keep the record with the higher value in field7, but if
field5 equals yes for one of the pair and no for the other, I want to keep
the one that has a yes, etc.etc.
My question is, how does access "see" a self-join? When I write a query like:
update Table.Field1 if Table.Field2< Table_1.Field2,
it only updates some of the records that fit the criteria. Even if I then
run a second query to update Table_1.Field1 if Table_1.Field2 < Table.Field2,
it still does not capture all of the records that it should. I can't seem to
find any info on how self joins work exactly, any suggestions would be
appreciated.

 

[John Spencer]

It sees a self-join in the same way it sees any other join.

Think of it this way. You have to IDENTICAL tables with the same
information in both tables the two tables only differ in the name of the
table. If you join those two tables that is the same as doing a self-join.

As far as your problem goes, you really need to (1) post the SQL statement
you are using, (2) give an example of a record that gets updated and one
that doesn't get updated (even if you think it should).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Sheridan]

A self-join joins one instance of a table to another instance of the same
table on one or more columns and returns the rows which match. You might use
it for instance to return a set of rows from an Employees table with each
employee's name and the name of their supervisor by joining on the EmployeeID
and SupervisedBy columns, the latter being a column which refernces the
EmployeeID primary key of the table, e.g.

SELECT E1.EmployeeName As Employee,
E2.EmployeeName As Supervisor
FROM Employees AS E1 INNER JOIN Employees AS E2
ON E2.SupervisedBy = E1.EmployeeID;

In your case, because you want to update a column in the table you need to
specify which instance of the table the column you want to update is from,
identifying each instance by an alias, D1 and D2 below:

UPDATE
Duplicates AS D1 INNER JOIN Duplicates AS D2
ON D1.Field3 = D2.Field3 AND D1.Field2 = D2.Field2 AND D1.Field1 = D2.Field1
SET D1.Keep = TRUE
WHERE (D1.Field5 = D2.Field5 AND D1.Field7 > D2.Field7)
OR (D1.Field5 <> D2.Field5 AND D1.Field5 = TRUE);

where Duplicates is the name of the table and Keep is a Boolean (Yes/No)
column you are using to mark the rows to be kept. This would also cater for
Field5 being False (No) in both rows of a pair, if this is a possibility,
setting the keep column to TRUE for the row with the highest value in Field7
again.

The WHERE clause identifies the rows from D1 where either the values of
Field5 are the same and the value of Field7 from D1 is greater than that of
Field7 from D2, OR the values of Field5 from each instance are not the same
and the value of Field5 from D1 is TRUE. Note that each part of the OR
operation is included in parentheses; this forces the AND operations within
each set of parentheses to evaluate first, before the OR operation.

Because you are joining two instance of a table with pairs of matching rows
twice the number of rows in the table would be returned if the query were
unrestricted by a WHERE clause, each row of a pair being joined to itself and
to the other matching row. The WHERE clause evaluates to TRUE for only one
out of each subset of four rows, and it is in this row that the value of the
Keep column from the D1 instance of the table is set to TRUE.

Ken Sheridan
Stafford, England