Left outer join with two key fields, MakeQueryTable ?

Question

Hello,

I???m quite new to both Python and ArcGIS and I can???t figure out how to solve the following problem:
I have two tables  (???A??? and ???B???) which both have a first key_field (???number???) and a second key field ???subnumber???.  Table ???A??? has more records than table ???B???. 
Now I have to select all those records from table ???A??? that are NOT contained in table ???B??? too.  (Later on I have to loop through those records.)

I tried the following statement:
arcpy.MakeQueryTable_management(tableList, ???query1???,"USE_KEY_FIELDS", key_fields_List, ??????, whereClause)

tableList: table ???A??? and ???B???
query1:  new Table view
key_fields_List:  A.number, A.subnumber,B.number, B.subnumber
where clause: B.number is NULL

I get the following error message: An invalid SQL statement was used. [query1]. 
As soon as I run it without the key_fields_List and a where_clause, it runs ok and the two tables get joined (somehow???).  I guess it???s my key_fields_List that???s wrong??? (I tried it just with "number, subnumer" as well, but I get the same error.)
Does anyone know how I have to define the different terms of the MakeQueryTable-statement? Or is there a different way how I could solve my problem?

Thanks for your help
Vera

VeraDiaz-Köhli · Answer

Hi Marc

Thanks for your reply, especially for the hint with the where_clause, but it still doesn't work.

I use variables for the tables and fields and what confuses me now is how to build the where clause, with quotes or without.

I tried all kinds of versions, but none would work.

I get the following error messages:
An invalid SQL statement was used. [QueryTable1]
An invalid SQL statement was used.
Failed to execute (MakeQueryTable).

Here's my code:

# path to the file-geodatabase
pfad_gdb = "H:\Moorinventar\FM_OBJEKTBLAETTER\Data\FM_OBJEKTBLAETTER.gdb\"

# the two tables within the file-geodatabase
table_TEILOBJ =  pfad_gdb + "FM_TEILOBJ_REV_2007_MIT_HM"    # table A
table_HM_FM =  pfad_gdb + "HM_FM_VIEW"     # table B

# the fields to use for the join
Field_TeilbOj_ObjNr = table_TEILOBJ+".FM_OBJ"   # table_A.number
Field_TeilbOj_TObjNr = table_TEILOBJ+".FM_TOBJ" # table_A.subnumber
Field_HMFM_ObjNr = table_HM_FM+".FM_OBJ"  # table_B.number
Field_HMFM_TObjNr = table_HM_FM+".FM_TOBJ" # table_B.subnumber

# key field
key_field= Field_TeilbOj_ObjNr

# no field list, as all the fields are needed

# where clause
where_clause_ohne_HM= """+Field_TeilbOj_ObjNr + "" = ""+Field_HMFM_ObjNr+"" AND ""+Field_TeilbOj_TObjNr+"" = ""+ Field_HMFM_TObjNr+"" AND ""+Field_HMFM_ObjNr+"" IS NULL"

arcpy.MakeQueryTable_management([table_TEILOBJ,table_HM_FM], "QueryTable1", "USE_KEY_FIELDS",key_field, "",where_clause_ohne_HM)

Thanks in advance! 
Vera

MarcNakleh · Answer

Hello there,

I don't know if this is just a rendering thing, but your code seems to have unicode quotes (�??�?�, a.k.a. \201C, \201D), as opposed to regular quotes (""), which may cause some issues when using arcpy. I've had some minor issues in the past using these in larger scripts and tools....
In either case, would it be possible to see the exact code you are using? It's sometimes hard to know whether there's something hiding behind metacode.

A description of how to use arcpy.MakeQueryTable_management (though with aliases) can be found on ESRI's usage page for Make Query Table
A big issue to note is that your WHERE clause is being used to filter values but also to define the JOIN parameters between your two tables.

One of the examples on ESRI's page has a WHERE clause that looks like:whereClause = "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips and vtest.COUNTIES.STATE_NAME = 'California'"

that both joins on COUNTIES.FIPS = CODEMOG.Fips, but also filters based on STATE_NAME = 'California'

So, unless I'm mistaken, your WHERE would need to look roughly something like thiswhereClause = "A.number = B.number and A.subnumber = B.subnumber and B.number IS NULL"

which should return the rows where there is no B.number matching that particular A.number (i.e. A's value is not present in B.) However, that's assuming it acts as a Left or Full Outer Join. If not, could compare the values that are still present in A.number with your original values in A.number using a Search Cursor, or some other means.

Also worth checking: this older thread on the same topic.

I hope this helps!
Marc