Pitney Bowes
MapInfo Pro™ Monthly Journal
"Be the Query Ninja" - Introducing Sub-selecting

64 bit This is the first part in what will be a series on Sub-selecting. Using Sub-selects is an advanced querying technique can be very handy. Technically speaking using a sub-select involves embedding a query inside another query. The results of the embedded query are evaluated against the "main" query.

We will start with an example that is fairly easy to understand. Let's say you have a dataset of numbers. The numbers can be population (or some other demographic), sales figures, expenditures, a KPI score, an index of some type, patient counts, insurance premiums written, or some other data. Let's also say you want to make a distinction on the map of all of the records above the average value. For example, which regions have a population greater than the average population of all the regions.

A two step method (the conventional way)

If you are comfortable with using the SQL Select capabilities in MapInfo Professional you can probably quickly come up with a way to do this in two steps. Your first step would be to calculate the average. You could do this with a quick SQL Select command or with the Query > Calculate Statistics command. (If you are unfamiliar with the basics of using the SQL Select capabilities, this article provides an introduction.)

That first step will give you the average value. You would then run a second query to select all of the values above (or below) that average, as desired. The resulting query could then be mapped or analysed further. All that was just to give you an idea of how we might use a sub-select.

Sub-selecting - using a query within a query

The same result described above can be done in a single query by using a sub-select. Once again, the technique of sub-selecting allows the embedding of one query within another. The results of the second embedded query are returned and used in the main query.

Here is an example of what we might try to achieve with a sub-select described in somewhat "plain" English. Imagine you have a table of administrative boundaries with population.

"Select all of the Admin boundaries whose population is greater than the average population of all the Admin boundaries".

OK, that doesn't exactly roll off the tongue but hopefully all of that introduction helps to describe what we are trying to achieve with a sub-select. What we will do next is implement the above example in MapInfo Professional's SQL Select dialog box.

Sub-selecting in MapInfo Professional

To demonstrate this technique we will use a table of Output Area boundaries from the UK. The boundaries are from Reading and its surrounding area (not the entire country). The table includes the population of each boundary. The column used is Persons_all_Usual_Residents.

Window gallery
The Output Area boundaries are produced from data supplied by the Office of National Statistics and the UK Ordnance Survey. Contains National Statistics data © Crown copyright and database right 2012. Contains Ordnance Survey data © Crown copyright and database right 2012.

Here is how to construct the query in the SQL Select dialog box.

sql select

Note that the entire sub-select is enclosed within parenthesis and is a complete SQL statement of its own. No "into table" clause is required.

For you Scripting Samurais out there, here is how the Sub-select appears in the MapBasic window or in a Workspace:

Select * from Reading_Area_Output_Areas where Persons_All_usual_residents > (Select Avg(Persons_All_usual_residents) from Reading_Area_Output_Areas) into Greater_than_average

Viewing the results

All of the Output Areas with a population greater than the average population are selected. More information on our demographic data products can be found on our mapinfo.com here.

Note that the resulting query is named Greater_than_average. This query can now be used in a map. Below it is overlaid on top of the Bing Roads layer.

The Output area boundaries with a population greater than the average are shaded purple. The Bing Roads layer is part of the MapInfo Professional Premium Services. More info on the Premium Services offering can be found on our Web site here.

In the map above, the Greater_than_average layer is using the colour from column P row 1. The query layer is on top of the Bing Roads layer and has been set to be 80% translucent.

Coming up next...

There are other ways sub-selecting can be used. Now that we have some of the basics out of the way and have worked through what I hope is a fairly intuitive example, we will explore additional more complicated uses of this technique in future articles.

One area we will explore is tying together using the selecting Keyword In() which was introduced in last month's issue. This can be used to return a list of values instead of you having to type them out. Fawaz Bathiche, an accomplished MapInfo Professional user and MapBasic developer provided some examples in this post in our LinkedIn Group.

Once again, look for more on sub-selecting in future issues!

Article by Tom Probert, Editor of "The MapInfo Professional" journal

When not writing articles for "The MapInfo Professional", Tom enjoys talking to MapInfo Professional users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.