Pitney Bowes MapInfo Professional - Manual (Page 256)

County Customer Order # Pop_1990 Pop_1980 CountyName
Foster Francis 478001 27,135 23,789 Foster
Foster James 478002 34,846 35,456 Williamette
Mason Wickwire 478003 151,201 147,101 Mason
Counties Table Orders Table
Within the SQL Select dialog box, you use the Where Condition field to tell MapInfo Professional 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 Professional 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
Professional 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:
1. Join the tables with SQL Select.
2. Use Update Column on Selection. The update automatically takes effect in the appropriate base
table.
3. For more information, see Performing Outer Joins and Using the Instr Function to Find Data in the
Help System.
Finding Duplicate Values in a Column
Often data is entered into tables by many different users. Sometimes data is repeated, or there is common
information in several different records. This section explains how to find all rows in a table that, for a
given column, share a value with another row. This is accomplished by performing two SQL Select
statements.
The first SQL Select statement produces a query table with two columns. The first column is a list of all
unique values in the data column and the second column lists the number of times that each unique
value occurs. The second SQL statement compares each data column value with all rows in the Query
table where the count is greater than one.
In the next example, there is a table EMPLOYEE that has two columns Id_Num and Name.
To find the duplicate values, perform the two SQL Selects, modifying them where indicated.
1. On the Query menu, click SQL Select and fill in the SQL Select dialog box.
• Select Columns: ID_Num, Count(*)
• from Tables: EMPLOYEE
• Group by Columns: 1
MapInfo Professional 12.5 256
Querying Your Data in MapInfo Professional