Typically, you store your information in several different tables. You have your own data files, and you may also have various databases of statistical information that you purchased from MapInfo Pro. SQL Select allows you to create relational joins so that you can bring information from these various tables together into a single results table.
When you want to join two tables, you must determine whether one of the columns in the first table contains values that match one of the columns in the second table. Imagine that you have a table of counties that has demographic information - the population of people in various age ranges, ethnic groups, and occupational categories in each county. You may also have a database containing information about customer orders. You want to examine these two tables and see if certain kinds of orders come from counties having certain demographic characteristics. Perhaps you want to select counties according to combinations of orders and demographic characteristics. To do this you have to be able to join the two tables.
Suppose the counties table contains the name of the county. Similarly, one of the columns in the order table contains the name of the county in which the order originated. Thus, these two tables have one field in common, the county name. MapInfo Pro can use that common field to join the two tables.
CountyName | Pop_1980 | Pop_1990 | Order # | Customer | County | |
---|---|---|---|---|---|---|
Foster | 23,789 | 27,135 | 478001 | Francis | Foster | |
Williamette | 35,456 | 34,846 | 478002 | James | Foster | |
Mason | 147,101 | 151,201 | 478003 | Wickwire | Mason |
Counties Table Orders Table
Within the SQL Select dialog box, you use the Where Condition field to tell MapInfo Pro how to join the two tables. The SQL Select dialog box might look like this:
Select Columns: *
From Tables: Counties, Orders
Where Condition: Counties.CountyName = Orders.County
The order of the table names (in the From Tables field) is important. If both tables contain map objects, the results table will only retain the map objects from the first table listed in the From Tables field. Furthermore, when the query is complete, MapInfo Pro automatically selects some or all of the rows from whichever table is listed first in the From Tables field. Thus, in the preceding example, MapInfo Pro will select some or all of the rows from the Counties table. The results table will also include data copied from the Orders table, but the Orders table will not be selected per se.
When you join two tables, the number of rows in the results table depends on how well the two tables match up. Suppose you have an Orders table with 10,000 rows, and you join the Orders table to the States table, which has fifty rows. The results table may contain as many as 10,000 rows. However, if some of the rows in the Orders table fail to match any of the rows in the States table, the results table will contain fewer than 10,000 rows. Thus, if 400 of the rows in the Orders table do not have a state name (perhaps due to data-entry errors), and if the relational join relies on the state name, the results table may only contain 9,600 rows.
You can use Update Column to modify the results of an SQL Select multi-table join. When you want to update a column in one table with information from another table, you can:
- Join the tables with SQL Select.
- Use Update Column on Selection. The update automatically takes effect in the appropriate base table.