Join

The Join dialog alters a table or drawing, called the original table or drawing, by adding new fields populated with data taken from another table, drawing or image, called the joined table, drawing or image.  Existing fields in the original table or drawing can also be filled with data copied from the joined component.   The Join command provides classic joins between tables, and it also provides spatial joins between drawings and other drawings in a map, and between drawings and images in a map.

 

Behind the scenes, the dialog creates an update query using SQL joins to populate the new fields. Checking the Setup Join and Edit Query box will add any new fields required by the join and will open the update query in a Command Window.  We can adjust the update query as desired, or run it at any time to update the original table with any changes in the joined table.

 

 

Table to table:

 

  1. With the focus on the original table window, choose Edit - Join.

  2. In the upper right box choose the joined table or query from which fields will be joined.

  3. In the second row of boxes, choose the fields from each table that have common values.

  4. Click the Add button to choose a field from the joined table that will provide data for a new field in the original table.

  5. Double-click the new field name to specify a different name.

  6. Double-click the transfer method cell to choose a different transfer method, for example,  copy or average.

  7. Press Join.

 

The new field will be added to the original table, and populated with values from the joined table where there is a match in the two fields specified to have common values.  In the example above, a titles table for book titles will have added to it a publisher's name field taken from the publishers table, where the pub_id (publisher ID) is the same in both tables.

 

Drawing to drawing (spatial join):

 

  1. Open a map that has both the original drawing and the joined drawing as layers.

  2. With the focus on the original drawing layer in the map window, choose Edit - Join.

  3. In the upper right box choose the joined drawing from which fields will be joined.

  4. In the second row of boxes, choose the join method, for example, contained in or intersects.

  5. Click the Add button to choose a field from the joined drawing that will provide data for a new field in the original drawing.

  6. Double-click the new field name to specify a different name.

  7. Double-click the transfer method cell to choose a different transfer method, for example,  copy or average.

  8. Press Join.

 

Image to drawing (spatial join with an image):

 

  1. Open a map that has both the original drawing and the joined image as layers.

  2. With the focus on the original drawing layer in the map window, choose Edit - Join.

  3. In the upper right box choose the joined image from which fields will be joined.

  4. In the second row of boxes, the join method will always be, contains.

  5. Click the Add button to choose a channel from the joined drawing that will provide data for a new field in the original drawing.

  6. Double-click the new field name to specify a different name.

  7. Double-click the transfer method cell to choose a different transfer method, for example,  sample or average.

  8. Press Join.

 

 

Table to table - Copy data into an original field from the joined table:

 

  1. With the focus on the original table window, choose Edit - Join.

  2. In the upper right box choose the joined table or query from which fields will be joined.

  3. In the second row of boxes, choose the fields from each table that have common values.

  4. In the row for the field that is to be loaded, double-click into the rightmost column cell and choose the field from the joined table that will provide data.

  5. In that same row, double-click into the center column cell and choose the transfer method, for example, copy.

  6. Double-click a cell to choose a different source field (joined table) or to choose a different transfer method.

  7. Press Join.

 

Saving an update query:

 

  1. Arrange the Join dialog as desired for the join.
  2. Press Setup Join and Edit Query.
  3. That will add any new fields required to the target table, and will open the update query in a Command Window.
  4. With the focus on the Command Window, choose Edit - Save as Query to save the update query.

 

Update the original table using a saved update query:

 

  1. Right-click the saved update query and choose Run.

 

If either the original table or the joined table changes, running the update query will update the original table so it incorporates all changes in both tables.

 

See the Edit - Join topic for more info and examples.