Left Join

[Cory Martin]

I know that MapInfo Professional's SQL is somewhat limited, but can it
do a left join? If it can, what is the proper syntax to use?

Thanks,
Cory Martin

[Terry McDonnell]

Cory

As no SQL experts seem to be chirping in I'll take a look at your
problem. Could you outline what's needed and I'll see if I can come up
with a soln.

Terry McDonnell

[Tony Pilkington]

http://www.wellho.co.uk/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
may help you.

[Lars I. Nielsen (GisPro)]

Hi Cory,

MIPro cannot perform a left join, nor a right join, only inner joins are
supported..

However, since a left join is in effect the left table (when no join
occurred, i.e. with a null right table) + an inner join (when a join
occurred), it ought to be possible to emulate the result of a left join.

Best regards / Med venlig hilsen
Lars I. Nielsen
GisPro

[Cory Martin]

In a left join query, I would get back all of the rows of the first
table even if it didn't find its match in the other table(s). I don't
want to have to simulate a left join by adding new columns to the
original table and then updating these fields with values from the other
table(s) where the keys match. Unfortunately, MapInfo Professional
appears unable to perform any type of query other than a standard inner
join. I'm guessing it doesn't even support unions.

[Cory Martin]

I know how to do all the join types. But it doesn't seem that MapInfo
sports a modern (post-1992) SQL.

[Terry McDonnell]

Yes, I know what a left join is - I just wanted your special
requirement. Not only does MB not support what you rightly said, it
also can't handle querying from more than, say, 3 tables at once. So
I've had some experience of struggling with this inadequacy. Also, in
VFP, before joins were added to the SQL, I had to make do simulating L
joins (but I did at least have UNION there!).

So, you want all rows from L table, and some fields from R table,
presumably with those fields from R table empty where no match, but
filled where there's a match. I'm not sure how experienced you are with
MB either, and maybe you're as experienced as I am at doing this anyway.

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On
Behalf Of Cory Martin
Sent: 25 April 2007 15:01
To: Terry McDonnell

Subject: [MI-L] Re: Left Join

In a left join query, I would get back all of the rows of the first
table even if it didn't find its match in the other table(s). I don't
want to have to simulate a left join by adding new columns to the
original table and then updating these fields with values from the other
table(s) where the keys match. Unfortunately, MapInfo Professional
appears unable to perform any type of query other than a standard inner
join. I'm guessing it doesn't even support unions.

[Lars I. Nielsen (GisPro)]

True, but you can get by nevertheless, just be using the available
features smartly.

Or use a full scale backend sql server (MS/SQL, Oracle, Postgres or even
MySQL), at let it do the heavy sql work. This is how many applications
work these days.

Besides, I think MIPro SQL actually predates ANSI SQL 1992, not that it
matters much.

Best regards / Med venlig hilsen
Lars I. Nielsen
GisPro

[Cory Martin]

Oh, how I would love for MapInfo to work spatially with Postgres or MySQL! Small businesses simply can't afford Oracle Spatial.

I'm actually doing the really, really, REALLY heavy stuff in MySQL and Postgres. Then I have to join that data with tab files because the tabs have the geometry that MapInfo can use. I really need something much more seamless and efficient. I've started experimenting with Geoserver as a method to make spatial data in my Postgres databases available to MapInfo via WFS. I'm curious to hear what other solutions people have tried.

Best regards, / Met vriendelijke groet,
Cory Martin

[Richard Greenwood]

On 4/25/07, Cory Martin <cma...@maponics.com> wrote:
>
> Oh, how I would love for MapInfo to work spatially with Postgres or MySQL!
> Small businesses simply can't afford Oracle Spatial.
>
> I'm actually doing the really, really, REALLY heavy stuff in MySQL and
> Postgres. Then I have to join that data with tab files because the tabs have
> the geometry that MapInfo can use. I really need something much more
> seamless and efficient. I've started experimenting with Geoserver as a
> method to make spatial data in my Postgres databases available to MapInfo
> via WFS. I'm curious to hear what other solutions people have tried.
>
> Best regards, / Met vriendelijke groet,
> Cory Martin

Using Geoserver or MapServer to serve PostGIS results to MapInfo via
WMS is a cool idea! And I too would love to see MapInfo connect to
PostGIS.

My only comment is that the free Oracle XE includes "Locator", a
castrated version of Spatial.

Rich

--
Richard Greenwood
richard....@gmail.com
www.greenwoodmap.com

[Cory Martin]

Yeah, I looked into Locator. Unfortunately, my datasets are freakin' huge and the most important functions (to me, at least) available in Spatial have been left out of Locator. By the way, has anyone had success using MapInfo with Oracle Locator?

[Peter Horsbøll Møller]

Hi,

MapInfo supports some kind of Left join, it's call Add Column:

Add Column table ( column [ datatype ] )
{ Values const [ , const ... ] |
From source_table
Set To expression
[ Where { dest_column = source_column |
Within | Contains | Intersects } ]
[ Dynamic ] }

This will let you update an existing column or add a new temporary column to your table.
I know it isn't what you call a left join but it does the trick.

Peter Horsbøll Møller
GIS Developer, MTM
Geographical Information & IT

COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark

Tel +45 6311 4900
Direct +45 6311 4908
Mob +45 5156 1045
Fax +45 6311 4949
E-mail p...@cowi.dk
http://www.cowi.dk/gis

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of Lars I. Nielsen (GisPro)
Sent: Wednesday, April 25, 2007 4:21 PM
To: mapi...@googlegroups.com
Subject: [MI-L] Re: Left Join