Making Queries using the SQL Select Command - MapInfo_Pro - 2023

To create a query using SQL Select:

1. Open the table you wish to query, if you have not already done so. The table that you query is known as the base table. If you use SQL Select to perform a query on the World table, the World table is your base table.
2. In the Selection group on the TABLE/MAP/SPATIAL tab, click on the SQL list and then click on SQL Select. The SQL Select dialog box displays. Fill in the portions of the SQL Select dialog box that meet your needs. When you click OK, MapInfo Pro performs the query.

   MapInfo Pro extracts data from your base table, stores the query results in a special, temporary table, known as the results table. The results table contains only the rows and columns that meet your criteria. The default name of the results table is Selection (although you can specify a different results table name in the Into Table Named field in the SQL Select dialog box).

3. Open a Map window and/or a Browser window if you want to see the query results. By default, MapInfo Pro displays the results table in a Browser window automatically (unless you clear the Browse Results check box in the SQL Select dialog box).

   If your results table is called Selection (the default name), the Browser window shows a different table name, such as Query1 or Query2. This is because the moment you browse the Selection table, MapInfo Pro takes a "snapshot" of the table, and names the snapshot Queryn (where n is a number, one or greater). MapInfo Pro takes the snapshot because Selection is a special table name; Selection dynamically changes every time you select or de-select rows.

   In the SQL Select dialog box, you can enter a different name for your results table (for example, you can name your results table My_Query). This prevents MapInfo Pro from renaming your results table Queryn.

4. MapInfo Pro automatically selects all rows in the results table. Thus, after you perform SQL Select, you can perform operations on the entire set of selected rows. For example, you could apply a different fill color to all selected rows (on the SPATIAL tab, in the Create group, on the Style list, click Region Style), or you could cut or copy all selected rows.
5. Usually, any alterations you make to the results table are automatically applied to your original (base) table. For example, if you use the SQL Select command to select some of the rows from the Orders table, and then you delete some of the rows from your results table, MapInfo Pro deletes the corresponding rows from your base table (Orders). However, if your query produces subtotals, you can alter the results table without affecting the base table.
6. On the HOME tab, in the File group, click Save Copy As if you want to make a permanent copy of the results table. If you do not perform Save Copy As, the results table will be deleted when you exit MapInfo Pro.

See also:

Saving a Template

Making Queries using the Select Command

Making Queries using the SQL Select Command

Entering SQL Queries with Expert Mode

The Enter SQL Query dialog box allows the user to enter queries more complex than the wizard allows using the standard DBMS SQL syntax. Invoking this dialog box ends normal wizard processing (there is no way to back up from this dialog box). This dialog can be reached after establishing a connection to the DBMS and choosing the Open DBMS Table Options dialog. Click the SQL View button under Expert Mode.

To type in multi-line formatted SQL do the following:

• To get a new line press Enter.
• To tab press Ctrl+Tab.
• To cut and paste from the clipboard, press Ctrl+X to cut, Ctrl+Cto copy, and Ctrl+V to paste.

Using the SQL Select Dialog Box's Where Condition Field

The Where Condition field serves multiple purposes. On some occasions, you might use a Where Condition expression to filter your table, so that you see only the rows that meet certain criteria. On other occasions, you use the Where Condition field to specify a relational join, so that your query can include columns from two or more tables.

Using the SQL Select Dialog Box's Select Columns Field

Use the Select Columns field to specify which columns should appear in the query results table.

• If you want the results table to have the same set of columns as your original table, enter an asterisk (*) in the Select Columns field.
• If you want the results table to have a different set of columns than your original table, delete the asterisk and enter a comma-separated list of column names or expressions, as described below. The Select Columns field can include an asterisk or a list of column expressions, but not both. Fill in the From Tables field before you fill in the Select Columns field.

If your query involves more than one table, each column name must be preceded by the name of its table, with a period separating the table and column names. Thus, if you are performing a query that involves two tables, one of which is the Canada table, and you want the query to include the Population column from the Canada table, you must use the column expression Canada.Population. The Columns drop-down list automatically inserts the table name in front of each column name whenever you use two or more tables.

Updating a SQL Select Query Table

You can use Update Column on the result of an SQL Select multi-table join, provided the SQL Select statement does not have a Group By clause.

To update a column in one table with information from another table:

1. Join the tables with SQL Select.
2. On the TABLE tab, in the Edit group, click Update Column on the Selection table, and the update will automatically take effect in the appropriate base table. You can also access this command from the SQL list in the Selection group under the TABLE, MAP and SPATIAL tabs.