next up previous contents index
Next: Extracting Data From Databases Up: Managing Databases Previous: Appending Databases

Joining Databases

JOIN allows two databases to be joined to form a third. The join is performed when the value of the key parameter from the first file is equal to the value of the key from the second file. You also have the option to include records when the key value exists in only one file. The values of the parameters in the file where the key value is missing are set to NODATA. A subset of the parameters can be selected. If all the parameters of a database are selected, then the expressions from that database will be included in the third database. Duplicate parameter names are not allowed.

Suppose we join all parameters from PLAS1 and PLAS2.

       PLAS1                         PLAS2

       SHOT    TE   NE   IP          SHOT    QA    QM  BETA
       89001  3.1  2.1  410          89001  1.1  1.01   .01
       89003  3.3  2.3  430          89002  1.2  1.02   .02
       89003  3.0  2.0  400          89003  1.3  1.03   .03
       89004  3.4  2.4  440          89004  1.4  1.04   .04
       89006  3.6  2.6  460          89006  1.6  1.06   .06
                                     89003  1.0  1.00   .00
       Program <>: JOIN

       Include all records?                    N
       Replicate records?                      N
       Databases to join?                      PLAS1 PLAS2
       to form database?                       PLASMA
       Key parameter from PLAS1?               SHOT
       Key parameter from PLAS2?               SHOT
       Key parameter for PLASMA?               SHOT
       Do you want all parameters of PLAS1?    Y
       Do you want all parameters of PLAS2?    Y
       <PLASMA created.>
The result is:
       PLASMA

       SHOT    TE   NE   IP   QA    QM  BETA
       89001  3.1  2.1  410  1.1  1.01   .01
       89003  3.3  2.3  430  1.3  1.03   .03
       89003  3.0  2.0  400  1.0  1.00   .00
       89004  3.4  2.4  440  1.4  1.04   .04
       89006  3.6  2.6  460  1.6  1.06   .06

Note that 89002 is skipped because it's in only one of the files. If the option to include all records had been selected, then 89002 would have been included with TE, NE, and IP set to NODATA. Also 89003 appears twice (and in the order encountered in the original files) because each file had two 89003's.

JOIN has an option for handling repeated key values. Suppose you want to merge the following two databases, BURST and PLASMA, where the key parameter is SHOT:

       BURST                           PLASMA

       SHOT  BURST  GAMMA              SHOT  IP   TE  ...
       6000  1      .4                 6000  2.   3.
       6000  2      .5                 6001  2.1  3.2
       6000  3      .45
       6001  1      .39
       Program <> JOIN

       Include all records?                    N
       Replicate records?                      Y
       Primary database to join?               BURST
       with secondary database?                PLASMA
       to form database?                       PLSBURST
       Key parameter from BURST?               SHOT
       Key parameter from PLASMA?              SHOT
       Key parameter for PLSBURST?             SHOT

       <PLSBURST.DBC created.>
The result is:
   PLSBURST

   SHOT   BURST  GAMMA  IP   TE  ...
   6000   1      .4     2.   3.
   6000   2      .5     2.   3.
   6000   3      .45    2.   3.
   6001   1      .39    2.1  3.2
The primary database, BURST, is the one with repeated shot numbers. The values in the secondary database, PLASMA, are replicated for the repeated BURST shot numbers.

If shot 6000 were to appear twice in PLASMA, the second record of PLASMA's shot 6000 would not appear in PLSBURST at all.

If the option to include all records is selected with replicate, then shots appearing in the primary database only appear in the result. But if shots appear in the secondary database only, they will not appear in the result.


next up previous contents index
Next: Extracting Data From Databases Up: Managing Databases Previous: Appending Databases

Marilee Thompson
Fri Jul 11 17:05:56 EDT 1997