SQL Select in MapInfo Pro 2019: left and right join?

[Michele Burgalossi]

Hello, is it possible to create a SQL selection in MapInfo Pro 2019 with left or right outer join condition between two layers (such as MS SQL Server)?

Often I have to add all the fields of table 2 to table 1 and there is no perfect match between table 1 and table 2.

Many thanks in advance.

Michele

[Peter Horsbøll Møller]

Hi Michele

 

This is basically what Add Column does.

In the interface, you find this in the Update Column dialog when you specify to update a column in one table with data from another table

 

Peter Horsbøll Møller

www.precisely.com

 

—

Peter Horsbøll Møller

Principal Sales Engineer - Distinguished Engineer

Precisely.com

 

From: mapi...@googlegroups.com <mapi...@googlegroups.com> On Behalf Of Michele Burgalossi
Sent: 7. maj 2021 15:42
To: MapInfo-L <mapi...@googlegroups.com>
Subject: [MI-L] SQL Select in MapInfo Pro 2019: left and right join?

 

This message originated Externally. Use proper judgement and caution with attachments, links, or responses.

 

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/195a431d-9b74-4b99-b910-afc2aca6427an%40googlegroups.com.

[Michele Burgalossi]

Hello Peter, many thanks for your answer.

That's OK and fast for one column to add (from one table to another).

If I had 2, 3, 10 or more fields to add (in this left/right join)  the update column command it could be too slow as a process, I have to repeat Table=>Update Column for each field, each time..

Is it possibile to fast the process without coding or SQL window?

Thanks.

Michele

[Peter Horsbøll Møller]

Another option is to run your normal Select with your join condition, and then update the “left” table with data from the “right” table using the normal Update Column function, or via a single Update statement, via the query:

 

Select l.NAME As "lNAME", r.NAME As "rNAME

, l.ADDRESS As "lADDRESS", r.ADDRESS As "rADDRESS"

From LeftTable As "l", RightTable As "r"

Where l.ID = r.ID

Into qTOUPDATE NoSelect

 

Update qTOUPDATE

Set lNAME = rNAME,

    lADDRESS = rADDRESS

 

In the example above, I’m updating two columns but you can include as many as you want.

 

Let me know if that makes sense

To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/b30f3816-1131-4212-9813-e241d69b59a4n%40googlegroups.com.

[Michele Burgalossi]

Ok. Magic!

Many thanks Peter.

To create (before) 1, 2 ore more fields in the "left table"  automatically I have to pass to MapBasic coding, the SQL windows is usefull only for automations of selections and updates, right? 

Michele

[Peter Horsbøll Møller]

No, the SQL Window also support MapBasic statements so you can write small scrips using this window.

 

You can use the Alter Statement in the same script to add needed additional columns just before running the query below:

 

Alter Table LeftTable

(Add NAME Char(25), ADDRESS Char(80))

 

Select l.NAME As "lNAME", r.NAME As "rNAME

, l.ADDRESS As "lADDRESS", r.ADDRESS As "rADDRESS"

From LeftTable As "l", RightTable As "r"

Where l.ID = r.ID

Into qTOUPDATE NoSelect

 

Update qTOUPDATE

Set lNAME = rNAME,

    lADDRESS = rADDRESS

 

HTH

To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/f74b1c1c-e8ad-45d4-803e-2b138e08c69dn%40googlegroups.com.

[Michele Burgalossi]

Hi Peter, many many thanks!!