MapInfo Products KnowledgeBase

Product: MapInfo
Version: 5.x/6.x/7.x
Platform: Not Platform Related
Category: SQL Select\Select

Summary:
Order of tables when joining via the Query>SQL Dialog.

Question:
When joining two tables in the Query>SQL dialog, which table should be listed first in the "From Tables" section?

Answer:
When joining mappable tables, the map objects associated with the first table listed will determine the map objects of the resultant query.

For example, if table1 is made up of points and table2 is made up of regions, and table1 is listed first, the resultant query will be made up of point objects that satisfied the "where condition" (whether it's a geographical join like table1.obj within table2.obj (i.e. points within the region), or a columnar join (i.e. table1.Zipcode = table2.Zipcode).

If table2 is listed first, the resultant query will be made up of regions that satisfied the "where condition".

The order of columns in the resultant query will be the columns from the first table listed followed by the columns from the second table listed (if "Select Columns" is set to the default of an asterisk (*) which means the query will return all columns from both tables).

If the first table listed is not a mappable table, the map objects of the resultant query will be determined by the next mappable table listed.

Note: when joining a mappable table to a non-mappable table, only records from the mappable table are selected. This can cause problems when performing an outer join query to find records in the non-mappable table. The solution is to make the non-mappable table mappable via Table>Maintenance>Table Structure and checking the "make table mappable" check box. Then, query SQL joins will select records from both tables, allowing the ability to perform an invert selection to select all records from the previously non-mappable table, if it is listed first in the From Tables section.

If neither table is mappable, the order of the tables listed will only affect the order of the columns in the resultant query.

First Table Listed in "From Tables"	Second Table Listed in "From Tables"	Resultant Query Map Objects
Mappable Table ( i.e. Points)	Mappable Table (i.e. Regions)	Map Objects Associated with First Table (i.e. Points)
Mappable Table ( i.e. Regions)	Mappable Table ( i.e. Points)	Map Objects Associated with First Table (i.e. Regions)
Non-Mappable Table	Mappable Table	Map Objects Associated with Mappable Table
Non-Mappable Table	Non-Mappable Table	N/A

Last Modified: 07/10/2003 03:28:29 PM
Document URL: http://testdrive.mapinfo.com/techsupp/miprod.nsf/kbase_by_product/482342370EC138F585256C0E00612409

Order of tables when joining via the Query>SQL Dialog.^482342370EC138F585256C0E00612409^Y