As promised in my previous blog today we are going to look at using the SQL Select dialogue box to carry out database queries in MapInfo. Although SQL can a initially look complicated to the uninitiated if you persevere you will soon discover that it offers a great deal more flexibility and so is with the effort.
The sorts of reasons why you might choose to use SQL Select rather than the quicker and simpler Select option is that it will allow you to do things like - you can show aggregate figures, rename columns, sort your results by more than one column, just view selected columns from a table not all of them and so on.
When you select Query > SQL Select a dialogue box will appear which you need to work your way through inputting the appropriate data to allow MapInfo to carry out the search.
"Select Columns"
This is simply a list of the columns that you would like to use from the the table that you will select in the next box. The individual column names should be separated by commas.
"From Tables"
This requires you to simply enter a list of the names of the tables that contain the columns that you want to include in the search. Again separate different table names with commas. If you have opened the relevant tables before you select SQL Select then this should be autopopulated for you.
"Where Condition"
This is the box that you enter the conditions that you want to put on the search. It requires you to understand a little SQL which I will speak more about in an upcoming blog post but for the time being we'll just use a simple example.
If one of your columns contained the age of people (which I've inventively called AGE!) you could simply enter a condition like "AGE > 5", and the query would return all of the instances where someone was older than 5.
"Group By Columns"
This, and the following boxes are now asking you how you want to present the results of your query, not actually looking at the query itself. This is asking you how you want to group your results data.
You may well not want to group your results data at all in which case leave this blank otherwise you will need to enter what column's data you would like to group your data by.
A simple example of this would be if you had a list of the people, where they live by city and how old they are. If you search for people above 65 you may well be doing this so that you can find out how many pensioners there are in that specific town, rather than wanting the detailed list.
If you "Group By" the column name that contains the city names then the results will show a list of the various cities that appear int he database and then a count of how many people meet the criteria you have searched by, so in this case how many people over 65 there are in that city.
"Order By Columns" (First Instance)
This allows you to select which column you would like to order your data by. So using our example you might again insert the column which contains city names so that the results are shown by order of the city name. If you do this as standard MapInfo will put them in alphabetical order.
"Order By Columns" (Second Instance)
This allows you to sort your data using a secondary condition as well.
"Into Table Named"
The results of your query will be put into a new table. This is simply asking you to give that table a name.
Once you have completed the above then you click OK and MapInfo will carry out the query for you. Hopefully the above sounds relatively straight forward as carrying out these queries is as simple, or complicated, as you want to make it. Or, depending on your, view as simple or complicated as your knowledge of SQL will allow it to become.
Although this is a MapInfo Blog and not a SQL blog this is such a useful feature that I will be writing more about SQL in future.
The sorts of reasons why you might choose to use SQL Select rather than the quicker and simpler Select option is that it will allow you to do things like - you can show aggregate figures, rename columns, sort your results by more than one column, just view selected columns from a table not all of them and so on.
When you select Query > SQL Select a dialogue box will appear which you need to work your way through inputting the appropriate data to allow MapInfo to carry out the search.
"Select Columns"
This is simply a list of the columns that you would like to use from the the table that you will select in the next box. The individual column names should be separated by commas.
"From Tables"
This requires you to simply enter a list of the names of the tables that contain the columns that you want to include in the search. Again separate different table names with commas. If you have opened the relevant tables before you select SQL Select then this should be autopopulated for you.
"Where Condition"
This is the box that you enter the conditions that you want to put on the search. It requires you to understand a little SQL which I will speak more about in an upcoming blog post but for the time being we'll just use a simple example.
If one of your columns contained the age of people (which I've inventively called AGE!) you could simply enter a condition like "AGE > 5", and the query would return all of the instances where someone was older than 5.
"Group By Columns"
This, and the following boxes are now asking you how you want to present the results of your query, not actually looking at the query itself. This is asking you how you want to group your results data.
You may well not want to group your results data at all in which case leave this blank otherwise you will need to enter what column's data you would like to group your data by.
A simple example of this would be if you had a list of the people, where they live by city and how old they are. If you search for people above 65 you may well be doing this so that you can find out how many pensioners there are in that specific town, rather than wanting the detailed list.
If you "Group By" the column name that contains the city names then the results will show a list of the various cities that appear int he database and then a count of how many people meet the criteria you have searched by, so in this case how many people over 65 there are in that city.
"Order By Columns" (First Instance)
This allows you to select which column you would like to order your data by. So using our example you might again insert the column which contains city names so that the results are shown by order of the city name. If you do this as standard MapInfo will put them in alphabetical order.
"Order By Columns" (Second Instance)
This allows you to sort your data using a secondary condition as well.
"Into Table Named"
The results of your query will be put into a new table. This is simply asking you to give that table a name.
Once you have completed the above then you click OK and MapInfo will carry out the query for you. Hopefully the above sounds relatively straight forward as carrying out these queries is as simple, or complicated, as you want to make it. Or, depending on your, view as simple or complicated as your knowledge of SQL will allow it to become.
Although this is a MapInfo Blog and not a SQL blog this is such a useful feature that I will be writing more about SQL in future.
RSS Feed