MapInfo Pro

My question is about how to store data from three tables in to one. I have one table with data and two tables with boundaries, criteria to match tables is a SA2 code. I know how to combine boundary table with data table but how can i combine two different areas and one data table at the same time. Data table contains SA2 code for both areas.

 

Thank you.

Maybe I don't really understand what you are trying to do so let me ask a coupl eof questions:

Do you want your resulting "table" to have two spatial objects for each record?

Or is the challenge merely that your boundary data is separated into two tables?

I have one table which contains boundary data, and I have two separate tables with information which I want to attach , as an example one information table contains population data and another has data for income. I want to run one single SQL query which will result one table which will contain Boundary Data, Population Data and Income Data.

I can upload tables here which I am trying to use if that would help.

Thank you

OK, that might just work.

The trick when joining more than two tables is that you need to join the first with the second, the second with the third, the third with the fourth and so on. Personally, I haven't tried joining more than three tables.

You query might look like this:

Select *

From POPULATION, BOUNDARY, INCOME

Where POPULATION.SA2CODE = BOUNDARY.SA2CODE

And BOUNDARY.SA2CODE = INCOME.SA2CODE

Let me know if this works for you.

It didn't work. When I set the SQL Select and set all the conditions as per your advise, when I press verify Map Info tells me that syntax is correct, but after i press "OK" it gives me an error massage.

It's a bit hard to see but I think you need to change the Where condition. SelectedArea1 must be the second value in the first part of the Where statement and the first part in the second part.

Also make sure that SelectedArea1 is the middle table in the list of tables.

Try to open the MapBasic window and capture the SQL Select statement and then post that here in the thread. In that way we can see the exact statement.

Hi Peter

Thank you for your massage

Apologies for such slow response from my end I was sick and away from office so I could not run a test.

Today I tried to change Where condition as you advised. Putted SelectedArea1 in the middle of table list and having it in the middle of the statement in Where condition.

That's what MapBasic window gave me: Select * from _2016Census_T01_NSW_SA2, Selected_Area_1, _2016Census_T02_NSW_SA2 where _2016Census_T01_NSW_SA2.SA2_MAINCODE_2016 = Selected_Area_1.SA2_MAINCODE_2016 And Selected_Area_1.SA2_MAINCODE_2016 = _2016Census_T02_NSW_SA2.SA2_MAINCODE_2016 into Selection

Browse * From Selection

Why SelectedArea1 has to be in the middle of statement and table list? And how would statement change if I would want to add one or more information tables?

 

Thank you very much for your help.

 

I just had a play with this. The order of the tables aren't that important if only one of them have small objects.

But the order of the conditions in the Where field must reflect the order of the tables in the From Tables field.

I tried joining four tables. One with spatial data called POLYGON. And three with alfanumerical data only called M, K and M_K.

These two queries give the same result:

1:

Select * from POLYGON, M, K, M_K

Where POLYGON.KOMKODE = M.KOMKODE

And M.KOMKODE = K.KOMKODE

And K.KOMKODE = M_K.KOMKODE

into Selection

2:

Select * from M, POLYGON, K, M_K

Where M.KOMKODE = POLYGON.KOMKODE

And POLYGON.KOMKODE = K.KOMKODE

And K.KOMKODE = M_K.KOMKODE

into Selection

Notice how the order of the tables in the From part is similar to the order int he Where part

But this one fails:

Select * from POLYGON, M, K, M_K

Where M.KOMKODE = POLYGON.KOMKODE

And POLYGON.KOMKODE = K.KOMKODE

And K.KOMKODE = M_K.KOMKODE

into Selection

Notice how the order doesn't match. POLYGON is first in the From Tables but second in the Where part.

This will raise this error:

So in theory, you can add additional tables to the statement as long as you are able to join the new table to the previous table.

Ok I understand the sequence for SQL and I have tried proposed query. It did work, but only for the first part. It did connect POLYGON with M, but didn't connect the rest. So I tried to use POLYGON twice first filed (FROM Tables) and I did same for where part.

First try: From field: Selected_Area_1, _2016Census_T01_NSW_SA2, _2016Census_T02_NSW_SA2, _2016Census_T04A_NSW_SA2

Where condition: Selected_Area_1.SA2_MAINCODE_2016 = _2016Census_T01_NSW_SA2.SA2_MAINCODE_2016 And _2016Census_T01_NSW_SA2.SA2_MAINCODE_2016 = _2016Census_T02_NSW_SA2.SA2_MAINCODE_2016 And _2016Census_T02_NSW_SA2.SA2_MAINCODE_2016 = _2016Census_T04A_NSW_SA2.SA2_MAINCODE_2016

 

It did connect information and boundary in first part but no the other two tables.

 

And when I tried using Selected_Area_1 twice in both from tables field and where condition field it gave an error massage even though I used same logic as before. My where condition field followed the order of from tables field.

I think I need to have a look at the table, Dinar.

Could you upload them here? If you prefer, you can also email them to me: peter.moller@pb.com

Thanks

Hi Peter have sent you an email with files I am working with.

Thanks, and I had a look at them and made them work.

This is the final SQL Select statement:

Select *

From Selected_Area_1, _2016Census_T01_NSW_SA2

, _2016Census_T02_NSW_SA2, _2016Census_T04A_NSW_SA2

, _2016Census_T18A_NSW_SA2

Where Selected_Area_1.SA2_MAINCODE_2016 = _2016Census_T01_NSW_SA2.SA2_MAINCODE_2016

AND _2016Census_T01_NSW_SA2.SA2_MAINCODE_2016 = _2016Census_T02_NSW_SA2.SA2_MAINCODE_2016

AND _2016Census_T02_NSW_SA2.SA2_MAINCODE_2016 = _2016Census_T04A_NSW_SA2.SA2_MAINCODE_2016

AND _2016Census_T04A_NSW_SA2.SA2_MAINCODE_2016 = _2016Census_T18A_NSW_SA2.SA2_MAINCODE_2016

into Selection

I have also attached a SQL Template that you can use to load the query into the SQL Select dialog.

You really need to stay focussed with these tables that are quite similar and all quite long too.

I have tested the query in both MapInfo Pro 15.0 and MapInfo Pro 16.0. It works in both but v15.0 only includes the first 120 columns in the query. MapInfo Pro 16.0 includes all the columns in the query.

But if you save the result as a new table, not all the columns will be included. Only around 120 columns will be included in the saved table. The query does

OK now it makes sense. I am using version 15.0 at the moment, as I understand this version has a limit on columns it can process where a 16.0 version can process way more. And I assume same query approach applied in regards of table amount just following structure "From Tables" in "Where Condition" field.

But at the same time there is an issue if I would want to save it as csv. which would result same problem as 15.0 version by saving only around 120 columns instead of full data set?

Yes, v15.0 is limited to around 120 column. MapInfo Pro v16.0 allows up to 999 columns in a table. The limit of v15.0 comes in multiple ways: maximum number of columns (I think that around 250) and maximum record width which is around 64000 Kbytes per record.

I just tried it again and with MapInfo Pro 16.0 I am to save the query as a new table with all the 632 columns, but only if I saved it into an Extended MapInfo Table.

I am also able to export the query into a CSV file with all the 632 columns.

Hi Dinar,

Greetings!
I hope you got the resolution of your query.
Please mark the best answer by selecting "Make Best Answer" in the Reply menu.
It will help us and other users save their time.

For more information click here.

Regards

------------------------------
Aakash Singh
Pitney Bowes Software India Pvt. Lt
NOIDA
------------------------------

Original Message:
Sent: 09-14-2017 22:24
From: Dinar Zakirov
Subject: SQL Select for multiple tables

My question is about how to store data from three tables in to one. I have one table with data and two tables with boundaries, criteria to match tables is a SA2 code. I know how to combine boundary table with data table but how can i combine two different areas and one data table at the same time. Data table contains SA2 code for both areas.

 

Thank you.