Joining Tables Using SQL Select - MapInfo_Pro - 2023

When performing an SQL Select operation with multiple tables, MapInfo Pro 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 Pro 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 check box.

Example - Total Population and Area by Continent

Given that MapInfo Pro can aggregate data, we can compute total population and total area for every continent in the world. We want to create a table with the following information:

• Number of countries per continent (in alphabetical order)
• Total population per continent
• Total area of each continent

To create a table that captures this data:

1. Open the WORLD.TAB table, and maximize its display by clicking the Maximize button.
2. On the MAP tab, click SQL Select to display the SQL Select dialog box.
3. In the From Tables box, type WORLD (or choose WORLD from the Tables drop-down list).
4. In the Select Columns box, delete the * character.
5. From the Columns drop-down list, choose Continent.
6. From the Aggregates drop-down list, choose Count(*).
7. Press the Spacebar once and type the column alias "COUNTRIES" with quotation marks. Remember to always use a blank space to separate an alias from its expression.
   Note: The Continent Count(*) column will appear in the results table as COUNTRIES.
8. From the Aggregates drop-down list, select Sum.
9. From the Columns drop-down list, select the Population column. The cursor is now on the inside of the end parenthesis [)]. Move it to the outside using the Right Arrow key.
   Note: Sum (Population) creates a derived column that computes the world population.
10. Press the Spacebar once and type the column alias "Total_Pop" with quotation marks.
    Note: The Sum(Population) column appears in the results table as Total_Pop.
11. From the Aggregates drop-down list, select Sum.
12. From the Functions drop-down list, select Area. The cursor is now on the inside of the end parenthesis [)]. Move it to the outside using the Right Arrow key.
    Note: Sum(Area) creates a derived column that will compute the total area.
13. Press the Spacebar once and type a column alias "Total_Area" with quotation marks.
    Note: The Sum(Area(obj, "sq mi")) column appears in the results table as Total_Area.
14. In the Group by Columns box, choose Continent from the Columns drop-down list.
15. In the Order by Columns box, choose Continent from the Columns drop-down list.
16. In the Into Table Named box, type POPULATION as the name for this table.

    The Count(*) counts all the records in the table. However, since we are grouping the countries according to continent, MapInfo Pro reports the number of countries in each continent and puts it in at a column called COUNTRIES (the alias).

    • Select Columns: Continent, Count(*) "COUNTRIES", Sum(Pop_Rural) "TOTAL_POP", Sum(Area(obj, "sq mi")) "TOTAL_AREA"
    • from Tables: WORLD
    • Group by Columns: Continent
    • Order by Columns: Continent
    • into Table Named: POPULATION
17. Click OK.

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 
Us_custg.order_amt > 10000
Select * from Us_custg,States,City_125 
where Us_custg.state = States.state and Us_custg.order_amt > 10000 and 
States.state = City_125.state

Error Handling

If an invalid Where condition that uses an OR as a logical operator is detected, MapInfo Pro will indicate an error has occurred. Usually this error will display whenever MapInfo Pro cannot find a join between two tables. For example, if you have specified the following incorrect condition:

Select * from A,B where A.field1 = B.field1  or A.field1 = B.field2

This error message displays:

No join specified between A and B. Invalid join condition in Where clause