MapInfo Pro

 View Only
  • 1.  Multi-Table Query

    Posted 02-03-2023 10:41
    I know that this was asked previously but can't find the solution.  I often want to link a table of geo objects (e.g., zip codes) to another geo "boundary" table to select objects from the first table while also linking the first table to a third table to limit the selection (or also add column data).  Is there a simple option in SQL or must I do a multi-SQL (which doesn't work if primary product is "Query" table)?

    Rex Edwards
    Tysons Corner VA

  • 2.  RE: Multi-Table Query

    Posted 02-06-2023 01:39
    Hi Rex

    You can join 3 (and more tables) in a single Select statement.
    The trick is the join the first table to the second and the second to the third table.

    Something along these lines:
    Select *
    From tableA, tableB, table3
    Where tableA.OBJ Intersects TableB.OBJ
    And tableC.OBJ Intersects TableC.OBJ​

    I have used spatial conditions but you can also use attribute joins and/or mix the two types.
    Often the trick lies in ordering the tables in the correct order so that the second table listed can be joined to the first and the third table.

    One issue that you ran into earlier was hat you couldn't control which table to take the spatial object from. MapInfo Pro automatically handled this through the order of the tables. Since MapInfo Pro v2019, we have had the Object keyword which allows you to control what spatial object to use in the resulting query. That can be one of the existing spatial objects, or it can even be an object expression creating a "new" spatial object.

    With MapInfo Pro v2019, we also removed the limitation that prevented you from using a query in a join.

    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data