1

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. enter image description here

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?

| improve this question | |
3

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
| improve this answer | |
  • I am updating about 10 columns in the full code so I was trying avoid the update column method but I guess it's not avoidable. Thanks Peter. – GeorgeC Aug 8 '16 at 3:47
  • @GeorgeC, you can do the same via a join. USe the Select to join the two tables via the join condition. Specify which columns to use. That's the columns you want to update and read from. Once you have the query, use the normal Update statement to update all 10 columns in one go – Peter Horsbøll Møller Aug 8 '16 at 5:56
  • I just added a small example on how to do this with a Select and an Update – Peter Horsbøll Møller Aug 8 '16 at 6:03
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

| improve this answer | |
  • T_Bacon thank you this will be very helpful. Is there a way to do something similar to the following arcpy --- AddJoin_management (in_layer_or_view, in_field, join_table, join_field, {join_type}) where join_type is either KEEP_ALL or KEEP_COMMON? – GeorgeC Aug 4 '16 at 22:19
  • @GeorgeC, unfortunately MapInfo Pro doesn't support Left/Right Outer Joins which would be the SQL way to maintain all records from one of the two tables you are joining. – Peter Horsbøll Møller Aug 5 '16 at 7:06

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.