Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8904.ZIP
Filename : COMPARIN.ASC

Comparing Databases

Lena Tjandra

There may be times when you want to find out what
the differences are between two or more databases. This article tells
you what needs to be done to accomplish this.

Suppose we have two databases, Patient and Doctor. We would like
to find out if there are any patients who are not doctors, and if
there are any doctors who are not patients. In more abstract terms,
we are trying to find out what in A is not in B, and what in B is
not in A.

To do this, we need to first set up a relationship from A to B (Patient
to Doctor or vice versa). This will give us everything that matches
in both A and B. All we need to do now is take what's left over:
everything in A that does not have a match in B. Then, we reverse
the relationship and get everything in B that is not in A.

Figures 1 and 2 represent a listing of the two databases.

First off, we're going to use both databases and establish a unique
index for each. We'll index both databases on the uppercase of First
plus Last. This is so that John and JOHN are
treated as the same name. Also, we'll use the UNIQUE clause when
we index them because we only want the first occurrence of a match.

SELECT 2
USE Doctor
INDEX ON UPPER(First + Last) TO Name2 UNIQUE

SELECT 1
USE Patient
INDEX ON UPPER(First + Last) TO Name UNIQUE



Next, we set the relationship from Patient into Doctor and list the
first name and last name of patients who are not doctors.

SET RELATION TO UPPER(First + Last) INTO Doctor
LIST First, Last FOR UPPER(First) <> UPPER(B->First) TO PRINT



Reverse the relationship and list the doctors who
are not patients.

SET RELATION TO
SELECT 2
SET RELATION TO UPPER(First + Last) INTO Patient
LIST First, Last FOR UPPER(First) <> UPPER(A->First) TO PRINT
EJECT



The steps above will give us only those records which do not occur
in both databases:

Jim Davis
Rose Hill
David Jones

v