Mapbasic JOIN and retain unmatched records

Question

I need to run a JOIN in Mapbasic and retain all records of the first table as well as the matched records.

So

Select Cadastral_data_QLD_CADASTRE_DC.LOTPLAN
  , Austpost_Extract_08_01.Unit_Type
  , Austpost_Extract_08_01.Unit
  , Austpost_Extract_08_01.Unit_End_Alpha
  , Austpost_Extract_08_01.House_Number
  , Austpost_Extract_08_01.House_Alpha
  , Austpost_Extract_08_01.House_End_Number
  , Austpost_Extract_08_01.House_End_Alpha
  , Austpost_Extract_08_01.Street_Name
  , Austpost_Extract_08_01.Street_Type
from Cadastral_data_QLD_CADASTRE_DC, Austpost_Extract_08_01 
where Cadastral_data_QLD_CADASTRE_DC.LOTPLAN = Austpost_Extract_08_01.LotPlan 
into RAW_DCDB_AusPOST_MATCHED

Commit Table RAW_DCDB_AusPOST_MATCHED As "G:\Projects\2016\22_DSC_DCDB_Update\Working\Working_MixedProcess\RAW_DCDB_AusPOST_MATCHED.TAB" 
  TYPE NATIVE Charset "WindowsLatin1" Interactive

Results in all the non-matched records being saved but this results in the unmatched records being dropped.

In mapinfo desktop I can do an inverse selection and then join the result to the RAW_DCDB_AusPOST_MATCHED.TAB

but I can't do an inverse in mapbasic

I can't do a != in the select either as I need just the records that don't match at all...and I need the attributes of the records where lotplan matches.

What can be done in mapbasic to get all records like in a QGIS/ArcMap join?

Answer 1

Looking at your Select statement it seems as if you only are "fetching" a single value from the Cadastral_data_QLD_CADASTRE_DC table and adding this to the records in the Austpost_Extract_08_01 table.

If that's your goal you could use the Update Column instead and update a column in the Austpost_Extract_08_01 table with the lot plan from the Cadastral_data_QLD_CADASTRE_DC table.

The join used in Update Column will be the same as you are using in your join.

The result will be that only the lot plans that actually have a match, will have been updated. The lot plans that don't have a match, will have an empty value in the column updated.

As I mentioned in the comments you can achieve the same with a combination of a Join Select and an Update.

Here is a small example:

'**Joinging the two tables and only specifying the columns to read from or write to
Select Cadastral_data_QLD_CADASTRE_DC.LOTPLAN
  , Austpost_Extract_08_01.Unit_Type "Write1"
  , Cadastral_data_QLD_CADASTRE_DC.Unit_Type "Read1"
  , Austpost_Extract_08_01.Unit "Write2"
  , Cadastral_data_QLD_CADASTRE_DC.Unit "Read4"
  , Austpost_Extract_08_01.Unit_End_Alpha "Write3"
  , Cadastral_data_QLD_CADASTRE_DC.Unit_End_Alpha "Read4"
  , Austpost_Extract_08_01.House_Number "Write4"
  , Cadastral_data_QLD_CADASTRE_DC.House_Number "Read4"
from Cadastral_data_QLD_CADASTRE_DC, Austpost_Extract_08_01 
where Cadastral_data_QLD_CADASTRE_DC.LOTPLAN = Austpost_Extract_08_01.LotPlan 
into RAW_DCDB_AusPOST_MATCHED

'**Updating the matching records using a standard Update statement
Update RAW_DCDB_AusPOST_MATCHED
   Set Write1 = Read1
     , Write2 = Read2
     , Write3 = Read3
     , Write4 = Read4

Answer 2

You can do it in exactly the same way you would do it in MapInfo Pro by accessing the menu commands defined in the menu.def file. Specifically you want to use invert selection as you've already stated, this is defined as:

Define M_QUERY_INVERTSELECT 311

So, if you just add Include "Menu.def" at the top of your code file you can then get an invert selection with the code Run Menu Command M_QUERY_INVERTSELECT