![]() |
|||||||||||||||||||
Map Room Home page | |||||||||||||||||||
written by Nigel
James
|
|||||||||||||||||||
MAPINFO How to... | |||||||||||||||||||
No. 13: Using queries and SQL (non-geographic) to select from tables | |||||||||||||||||||
(References to tables, folders etc., are not applicable outside the Map Room, so if you are located elsewhere, you should substitute your own) | |||||||||||||||||||
Queries are a powerful and quick way to select records from a table. They can be used to display selected places, for example. Although objects can be selected manually, using queries is much quicker. For single-table selections of all fields a query can be used, but the more powerful SQL (Structured Query Language) select is needed for queries using several tables and/or more complex queries. Examples of both are given here. Both examples use the tables world.tab and worldcap.tab which ship with MapInfo. You can either enter the query statement directly or use assistance to build the query. Non-geographic and geographic queries |
|||||||||||||||||||
This simple query example shows how to select all World capitals with a population above 1,000,000. | |||||||||||||||||||
1. Open the table worldcap in a browser window. You will see it has a field containing the population, which will be used for the query. | |||||||||||||||||||
2. Open the Query menu and click Select. | |||||||||||||||||||
3. In the Select dialog, check that World is displayed in the Select Records From Table box. | |||||||||||||||||||
4. Click the Assist button, and in the Expression dialog, click Columns. Choose the Pop_1994 column. | |||||||||||||||||||
5. Now click the Operators list and choose the greater than ( >) sign. | |||||||||||||||||||
6. Now type 1000000 (no commas!) after the greater than sign and click OK. You will now see the expression in the That Satisfies box. (You could of course simply type it in if you know the syntax to use). | |||||||||||||||||||
7. Type million_cities in the Store Results in Table box and click OK (make sure the Browse results box is checked). | |||||||||||||||||||
8. You will now see a browser with the cities with more than 1000000 people displayed. You can save this table (File>Save copy as) if you wish to use it again. | |||||||||||||||||||
|
|||||||||||||||||||
This SQL example selects name and population of all capital cities below 5000000 population which are in countries with more than 10,000,000 population | |||||||||||||||||||
In this example, we only require two fields and the data to query is in separate tables. To perform this query you use SQL. | |||||||||||||||||||
1. If the worldcap table is not already open in a browser, open it now. Also, open the world map table (world.tab) in a browser. | |||||||||||||||||||
2. Open the Query menu and click SQL Select. | |||||||||||||||||||
3. The boxes are arranged to follow the logical sequence of a query (select from...where condition...group by etc). However the boxes cannot be completed in the same order, as the table(s) must be selected first (in fact, the cursor is positioned in the from Tables box when the dialog opens). | |||||||||||||||||||
4. With cursor in the From tables box, click the Tables listbox and click Worldcap to select it. It will appear in the from Tables box. Next, click the Tables listbox again and click World to add that as well. | |||||||||||||||||||
5. MapInfo will now automatically create a join, which specifies the columns in each table which contain the same data. This is how the records from one table are matched with those from the other table. | |||||||||||||||||||
6. Now we can specify the columns (fields) to select from the Worldcap table. The default asterisk means all columns, but we need just name and population, so delete the asterisk but leave the cursor in the Select columns box. | |||||||||||||||||||
7. Click the Columns listbox and then select Worldcap.Capital to enter it into the Select columns box, then click the Columns listbox again and select Worldcap.Cap_Pop. | |||||||||||||||||||
8. Now we can enter the where condition. What we are asking is: "Select name and population of cities from the Worldcap table where the city has a population less than 5,000,000 an is the capital of a country with a population greater than 10,000,000". | |||||||||||||||||||
9. To start building the SQL statement, click in the where Condition
box, then follow these steps:
The where Condition statement should read: Worldcap.Capital = World.Capital And (Worldcap.Cap_Pop <5000000 And World.Pop_1994 > 10000000) |
|||||||||||||||||||
10. We will order the columns by population, so click in the Order by Columns box and select Worldcap.Cap_Pop from the Columns list. | |||||||||||||||||||
11. Finally, we will name the selection "Selected capitals" so enter the table name as selected_capitals in the into Table named box (note the underscore which is required in table names) | |||||||||||||||||||
12. Now check that the Browse results box is checked and click OK to execute the query. | |||||||||||||||||||
The required capitals will now be selected and displayed in a browser. To save the table, use File>Save copy as. | |||||||||||||||||||
This is a brief and specific guide to this topic, for more general information, see: MapInfo l - an easy guide for new users | |||||||||||||||||||
Nigel
James
Bodleian Library 2001 |