Pitney Bowes MapInfo Professional - Manual (Page 253)

• Average (expression): calculates the average of the values in <expression> for all the records in a
group.
• WtAvg (expression): calculates the weighted average of the values in <expression> for all the records
in a group.
• Max (expression): finds the highest value in <expression> for all records in a group.
• Min (expression): finds the lowest value in <expression> for all records in a group.
For more about grouping and ordering your data, see the Help System.
Joining Tables Using SQL Select
When performing an SQL Select operation with multiple tables, MapInfo Professional needs to join
information from the records in the various tables. You may have a map table containing only graphic
objects and their names and a table of statistical data for some geographic region. You want to display
the statistical data on the map table. You could use SQL Select to create a query table in which your
statistical data and map data are joined in one table.
Whenever you are working with multiple tables, you must put a statement in the Where Condition telling
MapInfo Professional how to match up the rows in the different tables. For example, you have the WORLD
table that contains countries and a table of economic statistics (Eco_Stats), also broken down by country.
You want to create a query table that contains both sets of data:
• Select Columns: * (an asterisk indicates include all columns in the query table)
• From Tables: World, Eco_Stats
• Where Condition: World.Country = Eco_Stats.Country
The two columns that you want to match do not have to have the same name. For example, you have
a table of international customers (Int_Cust) that contains a sales territory column (TERRITORY). This
column contains continent names, since your company breaks up its sales territories according to
continent. If you wanted to temporarily join the two tables:
• Select Columns: *
• from Tables: World, Int_Cust
• where Condition: World.Continent = Int_Cust.TERRITORY
• into Table Named: WORLD_DENSITY
• Select the Browse Results checkbox.
For an example, see Example - Total Population and Area by Continent in the Help System.
Using the Where Condition
The order of fields used in the Join does not matter. Either of the following syntaxes is acceptable:
Select * from A,B where A.field1 = B.field1>
Select * from A,B where B.field1 = A.field1
However, keep in mind that when you switch the order of geographic operands, the geographic operator
must also change. The following statements will produce identical results:
Select * from states, cities where states.obj contains cities.obj
Select * from states, cities where cities.obj within states.obj
Order of Clauses
The order in which Join clauses are performed does not matter. For example, each of the following are
valid clauses:
Select * from Us_custg,States,City_125
where States.state = City_125.state and States.state = Us_custg.state and
Us_custg.order_amt > 10000
Select * from Us_custg,States,City_125
where States.state = City_125.state and States.state = US.custg.state and
253 MapInfo Professional User Guide
Chapter 9: Selecting and Querying Data