Finding values that are different between two tables in MapInfo/MapBasic based on a unique ID

Question

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."

• Peter Horsbøll Møller: 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
• J-Hurter: AFAIK they are all Alias variables.
• Peter Horsbøll Møller: 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 ?
• J-Hurter: 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.
• Peter Horsbøll Møller: I have tried formulating my suggestions into an answer below

Answer 1

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

Answer 2

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

• J-Hurter: 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.