Table calculation

Joining columns from other tables

The join operation enables you to read a column from a second table and join it into the current table.

To be able to join, you need a link between the tables. The link between tables is always made through a common Class or ID domain that exists in both tables; this common domain may be used either by one or both of the tables itself, or by a column in the tables. This link is also called the key through which joining can take place.

To join a column into a currently opened table:

you can use the Join Column wizard,
you can type a Join expression on the command line of the table window.

This topic contains the following sections:

Join methods to create links between tables
Using the Join Column wizard
Join expressions on the command line of a table window
Examples

Join methods to create links between tables

1. Two tables which use the same domain:

The tables have a one to one relation.

You don't have to specify a key.

The link between the tables can be directly created through the domain of both tables.

2. A column in the current table uses the same domain as the second table:

The relation between the column of the current table and the second, selected table is a many to one relation.

You will need to specify a key column from the current table, i.e. key 1.

The link between the tables can then be created through the domain of the specified key column in the current table (key 1) and the domain of the second, selected table (key 2).

3. The current table uses the same domain as a column in the second table:

The relation between the records in the current table and the values in the second selected table is possibly a one to many relation.

You will need a key column from the second, selected table, i.e. key 2.

The link between the tables can then be created through the domain of the current table (key 1) and the domain of the specified key column in the second, selected table (key 2).

Furthermore, when the classes or IDs in the key 2 column of the second table are not unique (i.e. one to many relation), then the values to be joined need to be aggregated by the groups of key 2.

You will then thus need to specify from the second, selected table a key 2 column (the 'group by' column), an aggregation method, and optionally a weight column.

When the classes or IDs in the key column of the second table are unique (i.e. one to one relation), you should only specify key 2.

4. A column in the current table uses the same domain as a column in the second table:

The relation between the records in the current table and the second, selected table is possibly a many to many relation.

You will need to specify both a key column from the current table, i.e. key 1, as well as a key column from the second, selected table, i.e. key 2.

The link between the tables can then be created through the domain of the specified key column in the current table (key 1) and the domain of the specified key column in the second, selected table (key 2).

Furthermore, when the classes or IDs in the key 2 column of the second table are not unique (i.e. many to many relation), then the values to be joined need to be aggregated by the groups of key 2.

You will then thus need to specify from the second, selected table, a key 2 column from the second table (the 'group by' column), an aggregation method, and optionally a weight column; and from the current table a key 1 column.

When the classes or IDs in the key column of the second table are unique (i.e. many to one relation), you should only specify key 2.

Tips:

Links between tables are always through Class or ID domains.
For more information on column aggregations, refer to Aggregate functions.

Using the Join Column wizard

The easiest way to join columns is via the Join Column wizard which is available on the Columns menu in a table window.

To start the Join Column wizard:

open the table into which you wish to join one or more columns of another table,
open the Columns menu of the table window, and choose the Join command.
the first page of the Join Column wizard appears.

Short explanation of the Join Column wizard:

Select table and column:

Select the table from which you wish to join a column into the current table.
From the selected table, select the column that you wish to join into the current table.

Join method:

Select one of the join methods, see the explanation above.

If you wish to create a link through the common Class or ID domain of both tables, i.e. both tables use the same domain, you will directly continue on the Output column name page.

Link between tables:

Select a key column from the current table (key 1), and/or a key column from the second, selected table (key2).

Aggregation method:

If the values to be joined need to be aggregated, select an aggregation function and, optionally, a weight column from the second table.

The Aggregation method page will not appear when an aggregation is not necessary.

Output column name:

Type, for the current table, a name for the output column that will contain the joined values.

For more information, refer to the Join Column wizard Help.

Join expressions on the command line of a table window

To join a column from another table into the current table you may also type a Join expression on the command line of a table window.

Syntax

The syntax of the join operation on the command line is:

(1)	OutColName	=	ColumnJoin(TableName, ColumnName)
(2)	OutColName	=	ColumnJoin(TableName, ColumnName, Key1)
(3)	OutColName	=	ColumnJoin2ndKey(TableName, ColumnName, Key2)
(4)	OutColName	=	ColumnJoin2ndKey(TableName, ColumnName, Key1, Key2)
(5)	OutColName	=	ColumnJoinAggFunc(TableName, ColumnName, Key2)
(6)	OutColName	=	ColumnJoinAggFunc(TableName, ColumnName, Key2, Weight)
(7)	OutColName	=	ColumnJoinAggFunc(TableName, ColumnName, Key2, [Weight], Key1)

where:

OutColName	is the output column name. Usually, this is the same as the column name that was chosen to be joined into the current table.
ColumnJoin	is the command to start the Join operation.
ColumnJoin2ndKey	is the command to start the Join operation and when you wish to use a column from the second table (key2) to make a link to the current table.
ColumnJoinAggFunc	is the command to start the Join operation and when you wish to use an aggregation, i.e. joining while using an aggregation function. Replace the AggFunc part by one of the following aggregation functions: Avg \| Cnt \| Max \| Med \| Min \| Prd \| Std \| Sum. To use for instance the Average function, the first part of the expression should thus look like ColumnJoinAvg(...)
TableName	is the name of the second table from which you wish to join a column into the current table.
ColumnName	is the name of the column, from the second table, that contains the data that you wish to join into the current table.
Key1	is a Class or ID column from the current table that is needed to create a link between both tables.
Key2	is a Class or ID column from the second table that is needed to create a link between both tables. If an aggregation has to take place, the values to be joined will be grouped and aggregated on this column.
Weight	is an optional parameter to specify a column from the second table which contains weight values to be used during the aggregation. When you do not wish to use weights, specify value 1.

Formula 1 represents the case where both tables have the same domain.

Formula 2 represents the case where a column of the current table has the same domain as the second table (i.e. many to one relation).

Formula 3 represents the case where the current table uses the same domain as a column in the second table and when the classes or IDs in that column in the second table are unique (one to one relation).

Formula 4 represents the case where a column in the current table uses the same domain as a column in the second table and when the classes or IDs in that column in the second table are unique (many to one relation).

Formula 5 represents the case where the current table uses the same domain as a column in the second table and when the classes or IDs in that column in the second table are not unique (one to many relation). The values will be aggregated during the join.

Formula 6 represents the same case as Formula 5, but now weight values are used during the aggregation.

Formula 7 represents the case where a column in the current table uses the same domain as a column in the second table and when the classes or IDs in that column in the second table are not unique (many to many relation). The values will be aggregated during the join, while, optionally, using a weight column.

Examples

Example 1:

The domain of the current table is the same as the domain of another table.

Table Landuse contains landuse classes and also lists the commercial value of these landuse classes (column CommVal).

The (raster or polygon) histogram of the Landuse map contains the area of each landuse class.

The column Area from the histogram will be joined into attribute table Landuse.

Landuse.tbt		Landuse.his/.hsa		Landuse.tbt after joining
Domain Landuse	CommVal	Domain Landuse	Area	Domain Landuse	CommVal	Area
Residential	1000	Residential	9920800	Residential	1000	9920800
Commercial	2000	Commercial	4131200	Commercial	2000	4131200
Industrial	5000	Industrial	2161600	Industrial	5000	2161600
Institutional	4000	Institutional	4918400	Institutional	4000	4918400
Agricultural	2000	Agricultural	10461600	Agricultural	2000	10461600

Open table Landuse as the current table.

When you wish to use the Join Column wizard, open the Columns menu in the table, and choose Join.

In the Join Column wizard:

Select table and column page: select histogram Landuse, and select column Area as the column to be joined.
Join method page: select the option Use Domain of current table (key1) and use Domain of selected table (key2).
Output column page: type a name for the output column that will contain the joined values, e.g. Area.

When you wish to type a Join expression on the command line of the table window, type one of the following expressions:

Area = ColumnJoin(Landuse.his, Area)

Area = Landuse.his.Area

Area = ColumnJoin(Landuse.hsa, Area)

Area = Landuse.hsa.Area

With the first and second formula, column Area from the raster histogram Landuse.his is joined into the attribute table.

With the third and fourth formula, column Area from polygon histogram Landuse.hsa is joined into the attribute table.

The second formula is an alias for the first formula.

The fourth formula is an alias for the thrid formula.

Example 2:

The domain of a column in the current table is the same as the domain of the second table.

Table Municipality contains a number of municipalities, the population of each municipality and a column indicating whether the municipalities are considered large, medium or small (column MunClass).

Table MuniSubs contains information for large, medium and small municipalities. It contains a column Subsidy which represents for instance expected subsidy figures for types of municipalities.

The subsidy figures in table MuniSubs will be joined into the Municipality table.

Table Municipality			Table MuniSubs
Domain Municipality	Population	MunClass	Domain MunClass	Subsidy
Municip1	99208	MunLarge	MunSmall	1000
Municip2	41312	MunMedium	MunMedium	2000
Municip3	21616	MunSmall	MunLarge	5000
Municip4	49184	MunMedium	...
Municip5	104616	MunLarge
...

Table Municip after joining
Domain Municipality	Population	MunClass	Subsidy
Municip1	99208	MunLarge	5000
Municip2	41312	MunMedium	2000
Municip3	21616	MunSmall	1000
Municip4	49184	MunMedium	2000
Municip5	104616	MunLarge	5000
...

Open table Municipality as the current table.

When using the Join Column wizard:

Select table and column page: select table MuniSubs as the second table, and select column Subsidy as the column to be joined.
Join method page: select the option Choose key Column from current table (key1) and use Domain of selected table (key2).
Link between tables page: on the left hand side of the page, select column MunClass as key1.
Output column page: type a name for the output column that will contain the joined values, e.g. Subsidy.

When you wish to type a Join expression on the command line of the table window, type:

Subsidy = ColumnJoin(MuniSubs, Subsidy, MunClass)

Example 3:

The domain of your current table is the same as the domain of a column in the second table.

Table Province lists for each province some information. The domain of this table is Province; the Province domain contains all provinces of a certain country.

Table Municipality contains population figures for each municipality (column Population). Furthermore, for each municipality it is known in which province it is (column Province). Column Population has a value domain, column Province has domain Province (i.e. the same domain as the domain of the other table).

The population column Population of table Municipality will be joined into the Province table. As a province usually contains more than one municipality, the population figures need to be aggregated during the join.

Table Province		Table Municipality
Domain Province	AnyInfo	Domain Minicipality	Population	Province
Prov1	1000	Municip1	9920	Prov1
Prov2	2000	Municip2	4131	Prov1
Prov3	5000	Municip3	2161	Prov2
Prov4	4000	Municip4	4918	Prov1
Prov5	2000	Municip5	10461	Prov3
...		...

Table Province after joining
Domain Province	AnyInfo	Population
Prov1	1000	18969
Prov2	2000	2161
Prov3	5000	10461
Prov4	4000	?
Prov5	2000	?
...

Note that the value for Prov1 is an aggregated value.

Open table Province as the current table.

When using the Join Column wizard:

Select table and column page: select table Municipality as the second table, and select column Population as the column to be joined.
Join method page: select the option Use Domain of current table (key1) and choose key Column from selected table (key2).
Link between tables page: on the right hand side of the page, select column Province as key2.
Aggregation page: select an aggregation function, e.g. Sum.
Output column page: type a name for the output column that will contain the joined values, e.g. Population.

When you wish to type a Join expression on the command line of the table window, use:

Population = ColumnJoinSum(Municipality, Population, Province)

where: Population is the output column name, Municipality is the table from which you wish to get a column, Population is the column to be joined, and Province is used as key2 (the 'group by' column).

Example 4:

The domain of a column in the current table is the same as the domain of a column in the second table.

In this example, we will use two tables with domain None:

It is possible to make a link between the tables through their common table domain None, but then, the values of the selected column to be joined will simply be written, record by record, from the second table into the first table.
To obtain more useful values, we will therefore use a column that exists in both tables and which can ensure a proper link between the tables.

Table Province contains information on provinces (column Province) and some other information. The domain of the table is domain None; the domain of column Province is Province.

Table Municipality contains information on municipalities and population figures for each municipality (column Population). Furthermore, for each municipality it is known in which province it is (column Province). Column Population has a value domain, column Province has domain Province (i.e. the same domain as the domain of the column in the other table).

Table Province			Table Municipality
Domain None	Province	AnyInfo	Domain None	Municipality	Population	Province
1	Prov1	1000	1	Municip1	9920	Prov1
2	Prov4	2000	2	Municip2	4131	Prov1
3	Prov3	5000	3	Municip3	2161	Prov2
4	Prov4	4000	4	Municip4	4918	Prov1
5	Prov5	2000	5	Municip5	10461	Prov3
6	Prov2	...	6	Municip6	8745	Prov4
7	...		7	...

Table Province after joining
Domain None	Province	AnyInfo	Population
1	Prov1	1000	18969
2	Prov4	2000	8745
3	Prov3	5000	10461
4	Prov4	4000	8745
5	Prov5	2000	?
6	Prov2	...	2161
7	...

Note that the value for Prov4 is listed twice; the value for Prov1 is an aggregated value.

Open table Province as the current table.

When using the Join Column wizard:

Select table and column page: select table Municipality as the second table, and select column Population as the column to be joined.
Join method page: select the option Choose key Column from current table (key1) and choose key Column from selected table (key2).
Link between tables page:

on the left hand hand side of the page, select column Province as key1.
on the right hand side of the page, select column Prov as key2.

Aggregation page: select an aggregation function, e.g. Sum.
Output column page: type a name for the output column that will contain the joined values, e.g. Population.

When you wish to type a Join expression on the command line of the table window, use:

Population = ColumnJoinSum(Municipality, Population, Province, 1, Province)

where: Population is the output column name, Municipality is the table from which you wish to get a column, Population is the column to be joined, Province is used as key2 (the 'group by' column), 1 is used to indicate that no weights are used, and Province is used as key1.