FROM ARCVIEW 3.0

 

Joining tables 

You can join a table to the active table based on the values of a common field found in both tables. Join establishes a one-to-one or many-to-one relationship between the destination table (the active table) and the source table (the table you are joining into the active table). Typically, the source table contains descriptive attributes of features that you wish to join into a theme's table so that you can symbolize, label, query and analyze the features in the theme using the data from your source table. 

To join two tables

  1. Open the tables you wish to join.
  1. Make the source table active.
  2. Click the name of the common field you wish to use.
  3. Make the destination table active.
  4. Click the name of the common field you wish to use.
  5. From the Table menu choose Join, or click the Join button .

The table that is active when you choose Join is the destination table. The last table that was active is the source table.

Examples

You have a dBASE file containing business data for each state in the US. To display this data on a view:

  1. Add a theme representing US states to your view if it doesn’t already have one.
  2. Click on this theme's name to make it active.
  3. Click the Open Theme Table button to open the attribute table for this theme.
  4. Add the dBASE file into your ArcView project as a table. In this table, click the name of the common field you wish to base the join on.
  5. In the theme's attribute table, click the name of the common field.
  6. Click the Join button to join the new table to the theme's table.
  7. Edit the theme’s legend to define which data will be displayed.

You have access to land parcel data in a database sever such as Oracle or Ingres. To retrieve tax code records from this database and display them on a view:

  1. Make the Project window active and choose SQL Connect from the Project menu to connect to the database and run an SQL query to retrieve the records. ArcView creates a new table containing these records. In this table, click the name of the common field you wish to base the join on.
  2. Open the table of a theme representing land parcels. In this table, click the name of the common field.
  3. Click the Join button to join the new table to the theme's table.
  4. Edit the theme’s legend to classify land parcels using the tax code field.

 

Choosing a common field for the join

Tables are joined based on a field that is found in both tables. The name of the field does not have to be the same in both tables, but the data type has to be the same. You can join numbers to numbers, strings to strings, booleans to booleans and dates to dates.

In the first example above, a field in both tables containing the state name could be used as the common field for the join. A field containing the FIPS code or the postal abbreviation for the state could also be used.

In the second example above, a field containing the land parcel number could be used as the common field.

Any field can be chosen as the basis for a join, not just ones that uniquely identify geographic features. Fields storing attributes like parcel owner name, vegetation type, building materials, population size, etc. can be used too. For example, you might have a source table containing detailed information about different types of vegetation, and a theme table with a vegetation type field. You could join the tables together based on vegetation type so that the detailed information about the vegetation can be displayed on the view.

I've set everything up but why is the Join option still dimmed out?

The Join option remains dimmed out if the source table already contains a join. For example, if you have a table detailing a soil classification, and you join a table containing vegetation types into it, and then try and join the soil classification table into another table, the Join option will remain dimmed out.

To see if your source table already contains a join, make it active and look at the Remove All Joins option in the Table menu. If Remove All Joins is not dimmed out, your source table does contain one or more joins.

To perform a join in this situation you can either choose Remove All Joins to remove any existing join(s) from your source table and then do the join or export the source table into a new table, add that table into your project, and then do the join. The latter option is more useful than the first because it ensures that all the data in your original table is joined to the destination table.

The destination table (the table that is active when you choose Join) can contain existing joins.

Spatial joins

If you join two theme attribute tables together using their Shape field as the common field, ArcView automatically bases the join on the spatial relationship between the features in the two themes. See Spatial join.

Editing joined data

When editing a joined table, you can only edit the fields in the destination table. Edits to the fields from source tables must be performed directly on those tables. You can then use Refresh from the Table menu to show the changes in the joined table.

How are joins saved?

When you save a project containing a join, ArcView saves the definition of the join rather than saving the joined data itself. The next time you open the project, ArcView rejoins any joined tables by reading their files from disk or re-executing the SQL queries they are based on. In this way, any changes to the source or destination tables that have taken place since they were last joined are automatically included in the project, and reflected in any views, tables, charts or layouts based on the joined data.

Because joining tables has no effect on their physical files on disk, you don't need to have write permission to files to be able to join them in ArcView. For example, if you are working with themes representing geographic data accessed across your network from a remote, read-only GIS database, you can still join your own data tables to these themes and work with your data spatially.

If you want to make a new table on disk to permanently store the joined data, you choose Export from the Table menu. You would do this, for example, if you wanted to use the table in another application.

To remove joined fields from a table you are working on, choose Remove All Joins from the Table menu.

When to "link" tables instead of joining them

Join establishes a one-to-one or many-to-one relationship between the destination table and the source table. However in some situations you may want to establish a one-to-many relationship between the destination table and the source table.

An example of a one-to-many relationship is building occupancy. One building, such as a shopping center, may be occupied by many tenants. You may want to join a source table of tenants to the attribute table of a theme representing buildings. However, if you perform this join ArcView will find the first tenant belonging to each building, but ignore additional tenants. In these cases, you should link the tables instead of joining them. See Linking tables.