FrontPage >> MapInfoRoadmap >> MapInfoResources >> KnowlegeBase >>

SQL

SQL is a very powerfull part of MapInfo Professional. And I think that many aren't aware of the power hidden within the syntax of SQL.

To many SQL is a way of extracting data from an alfanumerical database using certain conditions. Within MapInfo SQL not only looks at the alfanumerical data but also at the geographical part.

Syntax To many SQL/select is hidden behind the SQL Select dialog. If you open the MapBasic Window and then run a SQL Select statement from the SQL Select dialog, you'll see the SQL Select statement, and syntax, in the MapBasic window. Once you learn this syntax, you might find it easier to use the MapBasic window in stead of the dialogs, but this - of course - is all up to you, and in some cases the dialog is easier to use

Select expression_list
  From table_list
  [where expression_group]
  [Into resulttable [NoSelect]]
  [Group By column_list]
  [Order By column_list]

expression_list is a list of expressions resulting in a list of columns in the resulttable. So each resulting column can either be an existing column in the input table, a function, a aggreation or a combined expression.

You can also add an alias to these columns simply by adding a name in "" (double quationsmarks), see example where the function has been given an alias:

Select TOWNNAME, CartesianArea(OBJ, "sq km") "AREA_SQKM"
  From TOWNS_OF_DENMARK

If you select from several table, you need to add the table name as a prefix to each column This is mostly necessary if your tables have columns with the same names.

Select ZIPAREAS.NAME "ZIPAREANAME", ADDRESSES.HOUSENO "HOUSENO"
  From ZIPAREAS, ADDRESSES
  Where ZIPAREAS.ID = ADDRESSES.ZIP_ID

table_list is a list of tables. It can also just be one table which will often be the case. If you need to select from several tables (joining tables), you just separate the tables with a comma.

Each time you add a table, you need to specify the connection between that table and the prevois table in the list. This is done in the Where section, see examples

Joining two tables:

Select *
  From ZIPAREAS, ADDRESSES
  Where ZIPAREAS.ID = ADDRESSES.ZIP_ID

Joining three tables:

Select *
  From ZIPAREAS, ADDRESSES, ROADNAMES
  Where ZIPAREAS.ID = ADDRESSES.ZIP_ID
  And ADDRESSES.ROADNAME_ID = ROADNAMES.ID

The order of the tables is important when you need to get the geographical part from a certain table, and all the tables are mappable. If you join two tables, MapInfo will select the "OBJ" from the first table in the list, that is mappable. If you are joining three tables, MapInfo will take the "OBJ" from the middle table in the list.

expression_group

to be continued...

resulttable

to be continued...

column_list

to be continued...

column_list

to be continued...

comments:

SELECT Statements --kevins1966, Wed, 31 Jan 2007 04:46:07 -0600 reply
It would appear that MapBasic can only handle simple select statements and it requires the order of the tables in the from clause to be in a particular order relative to the set of conditions, though the rule is unclear.

Example:

the following fails

SELECT * FROM TableA?, TableB?, TableC? WHERE TableA?.TableB?_id = TableB?.TableB?_id AND TableA?.TableC?_id = TableC?.TableC?_id

Yet the following works

SELECT * FROM TableB?, TableA?, TableC? WHERE TableB?.TableB?_id = TableA?.TableB?_id AND TableA?.TableC?_id = TableC?.TableC?_id

However simply moving the table order as obove was not enough to get the following to work

SELECT * FROM TableA?, TableD?, Doc_Class, TableB?, TableC? WHERE TableA?.TableA?_id = TableD?.TableA?_id AND TableA?.Doc_Class_Id = Doc_Class.Doc_Class_Id AND TableA?.TableB?_Id = TableB?.TableB?_Id AND TableA?.TableC?_Id = TableC?.TableC?_Id AND TableD?.Obj INTERSECTS ( SELECT Obj FROM TableE? WHERE TableE?.TableA?_id = "000010")