As I said in my last blog post, while this blog is here to talk about MapInfo not SQL, as you develop your MapInfo skills its impossible not to learn to work with SQL more closely.
The previous post give you an overview of the basics of SQL. This post will give you more detail on the first of the five pieces of a SQL statement that I discussed in the previous post SELECT. Since other posts will, in due course, tackel the other parts of a SQL statement this post will only focus on the SELECT part of a SQL search in MapInfo.
The SELECT statement defines the columns within the various tables that you want to carry out your search on. At a more advanced level it also defines the calculation that you want to carry out your search on as you can carry out calculations on the columns that you are selecting before then searching the results of those calculations.
The use of SELECT is most easily explained by way or practical examples, so here goes -
1. Selecting a whole table
The simplest SELECT statement is SELECT *, this will select all columns within the table that you are working with, so, in short the whole table that you reference in the FROM section of the query.
2. Selecting certain columns or fields from within a table
SELECT column1, column2, column3 ....
This will only select the columns that you have specified. Remember that each individual column must be separated from the other columns that you are selecting with a commas. This will only select the columns that you haver requested from the table that you will refer to in the FROM section of your query.
3. Selecting individual fields from a variety of tables
This is a more complex search which it should be noted requires an understanding of JOINS, something we will cover in due course. However, with that caveat the syntax for a query that chooses a number of difference fields from different tables is:
SELECT column1.table1, column2.table2, column3.table3
This will choose column1 from table 1 and so on. Again you will need to be precise and ensure that the each column / table pair is separated by a full stop, and that each pairing is separated from other pairings with a comma.
Assuming that you have good data normalisation (put simply, the process of ensuring that you do not have duplicate data in your database) within your database you will probably be using this type of query frequently as the relevant pieces of information that you need are likely to be spread between multiple tables.
4. Selecting individual fields from a calculated column
This type of query can very rapidly become very complex. It uses standard mathematical operators to adjust the contents of a column. The operators used in the calculations are as follows:
Addition + column1 + 5 Adds 5 to each result
Subtraction - column1 - 5 Subtracts 5 from each result
Multiplication * column1 * 10 Multiplies each result by 10
Division / column1 / 5 Divides each result by 5
So, starting with a simple example, if a distance you are working with in MapInfo happens to have been recorded in miles but you want to see the output of the search in kilometres, then you would use a query as follows:
SELECT distance * 1.6093
A more complex example using a variety of the operators above could look like
SELECT ((column1*1.6093)-25), (column2/23)
5. Selecting Only Distinct Values In A Database
Finally I think that it is worth mentioning that if you are looking to get a list of all of the different entries in a database. For instance you might have a list containing the counties in which certain buildings are based. If there was then a requirement to obtain a list of just the counties in which these buildings where then a DISTINCT query would work well. The example below would bring up a list of all of the different (DISTINCT) entries that can be found in the tables selected.
SELECT DISTINCT column1 .....
Hopefully you will find this a useful introduction to SQL queries when you are working with MapInfo. As you can hopefully see already learning to use SQL can give you a great deal of control over your data and a very wide ability to manipulate it in anyway that you would like to.
For those of you who would like to find more information on using SQL in MapInfo the following article is a useful resource - http://www.sgsi.com/miusergroup/sgsi_sqlselect.htm
In the next blog in this series I will be looking at the FROM section of a SQL query.
The previous post give you an overview of the basics of SQL. This post will give you more detail on the first of the five pieces of a SQL statement that I discussed in the previous post SELECT. Since other posts will, in due course, tackel the other parts of a SQL statement this post will only focus on the SELECT part of a SQL search in MapInfo.
The SELECT statement defines the columns within the various tables that you want to carry out your search on. At a more advanced level it also defines the calculation that you want to carry out your search on as you can carry out calculations on the columns that you are selecting before then searching the results of those calculations.
The use of SELECT is most easily explained by way or practical examples, so here goes -
1. Selecting a whole table
The simplest SELECT statement is SELECT *, this will select all columns within the table that you are working with, so, in short the whole table that you reference in the FROM section of the query.
2. Selecting certain columns or fields from within a table
SELECT column1, column2, column3 ....
This will only select the columns that you have specified. Remember that each individual column must be separated from the other columns that you are selecting with a commas. This will only select the columns that you haver requested from the table that you will refer to in the FROM section of your query.
3. Selecting individual fields from a variety of tables
This is a more complex search which it should be noted requires an understanding of JOINS, something we will cover in due course. However, with that caveat the syntax for a query that chooses a number of difference fields from different tables is:
SELECT column1.table1, column2.table2, column3.table3
This will choose column1 from table 1 and so on. Again you will need to be precise and ensure that the each column / table pair is separated by a full stop, and that each pairing is separated from other pairings with a comma.
Assuming that you have good data normalisation (put simply, the process of ensuring that you do not have duplicate data in your database) within your database you will probably be using this type of query frequently as the relevant pieces of information that you need are likely to be spread between multiple tables.
4. Selecting individual fields from a calculated column
This type of query can very rapidly become very complex. It uses standard mathematical operators to adjust the contents of a column. The operators used in the calculations are as follows:
Addition + column1 + 5 Adds 5 to each result
Subtraction - column1 - 5 Subtracts 5 from each result
Multiplication * column1 * 10 Multiplies each result by 10
Division / column1 / 5 Divides each result by 5
So, starting with a simple example, if a distance you are working with in MapInfo happens to have been recorded in miles but you want to see the output of the search in kilometres, then you would use a query as follows:
SELECT distance * 1.6093
A more complex example using a variety of the operators above could look like
SELECT ((column1*1.6093)-25), (column2/23)
5. Selecting Only Distinct Values In A Database
Finally I think that it is worth mentioning that if you are looking to get a list of all of the different entries in a database. For instance you might have a list containing the counties in which certain buildings are based. If there was then a requirement to obtain a list of just the counties in which these buildings where then a DISTINCT query would work well. The example below would bring up a list of all of the different (DISTINCT) entries that can be found in the tables selected.
SELECT DISTINCT column1 .....
Hopefully you will find this a useful introduction to SQL queries when you are working with MapInfo. As you can hopefully see already learning to use SQL can give you a great deal of control over your data and a very wide ability to manipulate it in anyway that you would like to.
For those of you who would like to find more information on using SQL in MapInfo the following article is a useful resource - http://www.sgsi.com/miusergroup/sgsi_sqlselect.htm
In the next blog in this series I will be looking at the FROM section of a SQL query.
RSS Feed