1

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 – Peter Horsbøll Møller Feb 17 '16 at 20:20
  • AFAIK they are all Alias variables. – J-Hurter Feb 19 '16 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 ? – Peter Horsbøll Møller Feb 21 '16 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 Feb 24 '16 at 11:41
  • 1
    I have tried formulating my suggestions into an answer below – Peter Horsbøll Møller Feb 24 '16 at 12:45
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

0

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 Feb 18 '16 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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