Example: STAR JOIN with two keys required to match cases

This is similar to the previous example, except both key/value pairs are contained in the same lookup table. This syntax is less complex than standard SQL.

Data

Table 1. Case data file: fact.sav
Name1 Name2 Field1 Field2 Field3
Barney Jones 1 1 10/29/1987
Fred Smith 1 3 10/28/1986
Barney Jones 2 2 12/1/2001
Joe Smith 5 4 12/1/2011
Nancy Jones 99    
Table 2. Lookup table: two_keys.sav
Name1 Name2 City
Barney Jones Anytown
Fred Smith Centerville
Nancy Jones Metropolis
Joe Jones Anytown

Syntax

STAR JOIN
  /SELECT t1.Name1, t1.Name2, t1.Field1, t1.Field2, t1.Field3, t2.City
  /FROM '/myfiles/fact.sav' AS t1 
  /JOIN '/myfiles/two_keys.sav' AS t2 
   ON t1.Name1 = t2.Name1 AND t1.Name2 = t2.Name2
  /OUTFILE FILE=*.

Result

Table 3. Merged file
Name1 Name2 Field1 Field2 Field3 City
Fred Smith 1 3 10/29/1987 Centerville
Barney Jones 1 1 10/28/1986 Anytown
Joe Smith 5 4 12/1/2001 (blank)
Barney Jones 2 2 12/1/2011 Anytown
Nancy Jones 99 (sysmis) (sysmis) Metropolis
  • All records from the case data file are included; so “Joe Smith” is included even though there is no matching record in the lookup table.
  • Only records from the lookup table that match both keys in the case data file are included; so “Joe Jones” is not included.