Error in SQL query in mapbasic

Question

I am writing below SQL query in mapbasic to insert columns from two tables t1 and t2 into table Sites but error is occuring "Variable or field t2.CELLID not defined"

Table Sites (Source integer, N integer, LAT float, LONG float) Select t1.CELLID, t2.CELLID, t2.LATITUDE, t2.LONGITUDE from t1 left join t2 on t1.CELLID=t2.CELLID where t1.obj within zone into Sites

Can anyone tell me how to rectify it?

• T_Bacon: Unfortunately MapBasic only allows inner joins so you will not be able to perform a left, right, or full join directly like that though there are workarounds involving more steps. Also, it's not clear what zone relates to in the query... is that an object variable?

Answer 1

You can try this:

Table Sites (Source integer, N integer, LAT float, LONG float)

Select t1.CELLID "CellID1", t2.CELLID "CellID2", t2.LATITUDE, t2.LONGITUDE 
 From t1, t2 
 Where t1.CELLID = t2.CELLID 
 And t1.obj within zone 
 into __TO__INSERT
Insert Into Sites
 Select CellID1, CellID2, LATITUDE, LONGITUDE
 From __TO__INSERT

But notice that you will only insert the records where there is a match between the CellIDs in the two tables. As @Tom_Bacon mentioned, MapInfo SQL doesn't support outer joins.