Selecting from more that 2 tables

[kompas]

Hi All,

I've searched the list but haven't found the answer so let me ask you
here:

I have a mappable table A and only tabular tables: B, C, D, E, F, G,
H. Each of B-H tables contain statistics for different rows of table
A.

I want to have data from B-H tables added to A map. The joining key is
NAME field in each of the table.

When I use:

Select * from B, A, C where B.NAME = A.NAME And B.NAME= C.NAME into
Selection

I get only a subset of A table that have values in both B and C.

When I use:

Select * from B, A, C, D where B.NAME = A.NAME And B.NAME= C.NAME And
B.NAME= D.NAMEinto Selection

I get an error:

"Wrong tables joined. Wrong WHERE clause".

When I use:

Select * from B, A, C where B.NAME = A.NAME Or B.NAME= C.NAME into
Selection

I get an error:

"Not specified joining rule for A and B. Wrong WHERE clause".

QUESTIONS:
How to join all of them?
How not to loose records that have values only in part of B-H tables?
Is there a limit of the number of tables to be joined at one time?

TIA,
Jakub

[Spencer]

Unfortunately, MapInfo doesn't have a full-blown SQL engine built in.
There's just enough so that the document windows in a workspace can be
recreated.

You can join several tables if the join statements in the WHERE clause
are properly separated by parentheses, and occur in the same order the
tables appear in. Thus:

select * from b, a, c, d where (b.name=a.name) and (a.name=c.name) and
(c.name = d.name)

will produce a selection.

Unfortunately, since joins are always (implicitly) INNER joins, that
statement will never provide the result you want. A value missing
from C will mean that the selection table won't contain that value.

All this means is that you can only reliably join TWO tables and it
has to be an INNER join. In your example, you can get a result for
three tables, at least for the NAME values that appear in table A.

However, you may be able to use Add Column to get what you want.
You're going to need a separate Add Column statement for each column
of B through H that you want to have show up in the map.

Hope this helps
Spencer