I am comparing 2 tables which are mostly the same but there have been some amendments, in effect comparing a new version to the old one and displaying all the records with changes in a selection table based on a unique ID.

I run into problems with the following SQL part of the script:

Select * From selectedTableNew, selectedTableOld 
Where (selectedColumnNew = selectedColumnOld) 
And (aColNew <> aColOld) 
Into AmendedRecords

I receive the error

No join specified between selectedTableNew & selectedTableOld. Invalid join condition in WHERE clause."

  • Are some of the columns in your statement Alias variables and others not? I would expect you to only use Alias variables for all the column references in the SQL statement - or build it into a string and use Run Command to execute the SQL statement Commented Feb 17, 2016 at 20:20
  • AFAIK they are all Alias variables.
    – J-Hurter
    Commented Feb 19, 2016 at 16:32
  • selectedColumnNew and selectedColumnOld have been Dim'ed as Alias variables and assigned a combination of a table name and column name using this structure: selectedTableNew & "." & selectedColumnNew ? Commented Feb 21, 2016 at 15:05
  • They are now :) - but now it seems that comparing the 2 tables it does not look at the same column, ie the Where statement part. I do a PRINT routine after each record and seems to compare random records with each other. But I think this might be due to the fact some of the values in the SelectedColumns are not unique.
    – J-Hurter
    Commented Feb 24, 2016 at 11:41
  • 1
    I have tried formulating my suggestions into an answer below Commented Feb 24, 2016 at 12:45

2 Answers 2


Make sure that you are using Alias variables in your SQL statement. You might want to change it to something along these lines:

Dim columnNew, selectedColumnNew, selectedTableNew As String
Dim columnOld, selectedColumnOld, selectedTableOld As String
Dim aSelectedColumnNew, aSelectedColumnOld, aColNew, aColOld As Alias

-- Here you assign the values to the string variables
-- I'm assuming you are using a dialog to ask the ser for input
selectedTableNew = "SomeTable"
selectedColumnNew = "SomeColumn"
columnNew = "SomeOtherColumn"
selectedTableOld = "SomeTable2"
selectedColumnOld = "SomeColumn2"
columnOld = "SomeOtherColumn2"

-- Now you link the table and columns to create a Alias
aSelectedColumnNew = selectedTableNew & "." & selectedColumnNew
aSelectedColumnOld = selectedTableOld & "." & selectedColumnOld
aColNew = selectedTableNew & "." & ColumnNew
aColOld = selectedTableOld & "." & ColumnOld

Select * From selectedTableNew, selectedTableOld 
   Where (aSelectedColumnNew = aSelectedColumnOld) 
   And (aColNew <> aColOld) 
   Into AmendedRecords

This should give you the records where the "selected" columns are identical and where the "other" column aren't.

Make sure that SelectedColumnNew/-Old are the ID's that should be the same in the two tables


This could be solved with MapBasic like

SELECT * FROM TABLE_1 WHERE NOT table_1_column IN ( SELECT table_2_column FROM TABLE_2 ) INTO Selection

or as a code

SELECT * FROM TABLE_1 WHERE NOT table_1_column IN (
    SELECT table_2_column FROM TABLE_2
) INTO Selection

BROWSE * FROM Selection

or with SQL select

enter image description here

  • This is not what I'm after unfortunately as this SQL simply looks for new records in 1 column, instead of going through every column looking for changes in records between the 2 tables.
    – J-Hurter
    Commented Feb 18, 2016 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.