MapInfo, SQL data from multiple tab files which have the same columns

Question

New to the GIS forum so apologies in advance for any potential etiquette failures...

I'm working in MapInfo. I have multiple tables with the same structure. One of my columns is pipe diameter. I would like to do an SQL select for a particular pipe diameter or range of diameters i.e. 5m<= x <=10m across all the tables. Is this possible?

I am able to do it for one table and save the data into a new table file, then redo this using a Mapbasic script however, I then end up with lots of individual tables. I could do this and then copy and paste between the tabs however the data is very large so this is time consuming. Using the SQL tool, is it possible to select data from a particular column which reoccurs in multiple tables, and create a group selection across multiple tables which can be saved into a single table?

Feel free to ask any questions

Answer 1

in sql you can write something like this :

SELECT * FROM  x where pipedn >= 5 AND pipedn <= 10 

or

SELECT * FROM table x WHERE pipedn BETWEEN 5 AND 10

these examples show howto use WHERE

if all your tables are same you can do:

SELECT col1, col2 from  x UNION ALL SELECT col1 , col2 from table y 

this one select two table x and y and creates one results set , you can also SELECT INTO this to new table which then contains x and y table

see: http://msdn.microsoft.com/en-us/library/ms180026.aspx

You can create new table all_data

CREATE TABLE all_data AS (SELECT id , data from x UNION ALL SELECT id, moredata as data from y)

for example http://www.techonthenet.com/sql/index.php seems to have quite good tutorial for basic SQL commands

edit: You can do something like this too

SELECT * from table_data x where x.col1 > 3 

table_data x creates alias x for table_data table name, so only thing you need to change between queries is table name. Howto do this in MapInfo , i dont know. Above is just simple SQL