Query Sql Error

Question

I want to join these 5 tables with MapBasic, because i want to use in statistic :

Select * 
 From Malade, Maladie, Ilot, Etat_Ilot, Pauvreté
 Where 
 Malade.Maladie = Maladie.ID_maladie 
 And Malade.Ilot = Ilot.ID_Ilot 
 And Malade.Classe_Pauvreté = Pauvreté.Classe 
 And Ilot.Etat_Ilot = Etat_Ilot.ID_Etat_Ilot 
 Into TIAC_EXP

When i excute it i get this error message:

"Incorrect tables are joined, Invalid Join Condition in WHERE Clause"

Can you tel me why ?

• Vince: Please edit the question to give some indication of the database in use, and why this is appropriate to a GIS (vice database) forum.

Answer 1

The SQL language of MapInfo Pro/MapBasic is some what limited when it comes to joins.

You can join multiple tables, but there is one basic rule that you need to follow, and looking at your SQL Select I doubt it's possible:

You need to "join" the first table to the second table, the second table to the third table, and so on.

Like this:

Select *
 From TABLEA, TABLEB, TABLEC, TABLED
 Where TABLEA.ID = TABLEB.AID
 And TABLEB.ID = TABLEC.BID
 And TABLEC.ID = TABLED.CID
 Into SOME__RESULT

In your case you probably need to do the joins in two or three SQL Select statements and save each result into a temporary table and then join the result set to each other:

Select * 
 From Maladie, Malade, Ilot, Etat_Ilot
 Where Maladie.ID_maladie = Malade.Maladie 
 And Malade.Ilot = Ilot.ID_Ilot 
 And Ilot.Etat_Ilot = Etat_Ilot.ID_Etat_Ilot 
 Into TIAC_EXP_A
Commit Table TIAC_EXP_A As "C:\TIAC_EXP_A.tab"
Close Table TIAC_EXP_A
Open Table "C:\TIAC_EXP_A.tab"
Select * 
 From TIAC_EXP_A, Pauvreté
 Where TIAC_EXP_A.Classe_Pauvreté = Pauvreté.Classe 
 Into TIAC_EXP

Something like the above