MapInfo Pro

Expand all | Collapse all

SQL Select for multiple tables

  • 1.  SQL Select for multiple tables

    Posted 09-15-2017 02:24

    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.



  • 2.  RE: SQL Select for multiple tables

    Posted 09-15-2017 03:21

    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?



  • 3.  RE: SQL Select for multiple tables

    Posted 09-18-2017 20:07

    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.



  • 4.  RE: SQL Select for multiple tables

    Posted 09-18-2017 20:15

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

    Thank you



  • 5.  RE: SQL Select for multiple tables

    Posted 09-19-2017 02:58

    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.



  • 6.  RE: SQL Select for multiple tables

    Posted 09-19-2017 21:16

    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.Dinar TestDinar Test_2Dinar Test_3



  • 7.  RE: SQL Select for multiple tables

    Posted 09-20-2017 02:52

    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.



  • 8.  RE: SQL Select for multiple tables

    Posted 09-28-2017 19:09

    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.

     



  • 9.  RE: SQL Select for multiple tables

    Posted 09-29-2017 05:52
      |   view attached

    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:

    Incorrect Tables Joined

    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.



  • 10.  RE: SQL Select for multiple tables

    Posted 10-02-2017 20:25

    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.



  • 11.  RE: SQL Select for multiple tables

    Posted 10-03-2017 02:16

    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



  • 12.  RE: SQL Select for multiple tables

    Posted 10-04-2017 18:17

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



  • 13.  RE: SQL Select for multiple tables

    Posted 10-05-2017 02:38
      |   view attached

    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.

    Multiple Table Joins

    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



  • 14.  RE: SQL Select for multiple tables

    Posted 10-05-2017 19:16

    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?



  • 15.  RE: SQL Select for multiple tables

    Posted 10-06-2017 04:04

    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.



  • 16.  RE: SQL Select for multiple tables

    Moderator
    Posted 08-05-2019 22:57
    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
    ------------------------------