Example: STAR JOIN with two key-value pairs in the same file

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: mixed_values.sav
Field1 Value1 Field2 Value2
1 One 1 A
2 Two 2 B
3 Three 3 C
99 Missing 5 E

Syntax

STAR JOIN
  /SELECT t1.Name1, t1.Name2, t1.Field1, t1.Field2, t1.Field3, 
   t2.Value1, t3.Value2
  /FROM '/myfile/fact.sav' AS t1 
  /JOIN '/myfiles/mixed_values.sav' AS t2 
   ON t1.Field1 = t2.Field1
  /JOIN 'myfiles/mixed_values.sav' AS t3 
   ON t1.Field2 = t3.Field2
   /OUTFILE FILE='/myfiles/merged_file.sav'.

Result

  • The result is exactly the same as the previous example.
  • The syntax is very similar to the previous example. The only difference is that both of the JOIN statements refer to the same lookup table, but each one assigns a different alias to the same lookup table.