Primary usage of ArcGIS Desktop Basic: I have to find the intersection of two tables in the same file geodatabase based on the values in multiple fields. Almost all queries to find some help are multiple field queries in one joined table or spatial in nature. The first is just wrong. For the second, I can't assume a spatial accuracy in Table B (below), some elements have already shown to be out side of their geographic range. And that's ultimately the end goal of the project: to improve the spatial accuracy of Table B using Table A.
I have to find the records in A that match records in B, but not all records in B are in A.
Tables: A (0.5M records) B (0.4M records)
Fields to return: A.1, A.10, A.11 B.1
Fields to match. I have assured myself that the fields are of the same type (Text/String, Integer, etc) and that the pairings below are type and value matched. Some of the records in A are going to match some of the records in B.
A.2=B.2 A.3=B.3 A.4=B.4 A.5=B.5 A.6=B.6
Using the ESRI "Make Query Table" in 10.2 basic, I used this as the query:
`A.2=B.2 and A.3=B.3 and A.4=B.4 and A.5=B.5 and A.6=B.6`
Didn't specify any keys.
This returns with the message that there is an error with the expression and invalid SQL statement used.
Making the table on just field 6 takes less than a second but the table fails to open. Field 6 is the least granular.
I have expended my knowledge and feel I won't find the answer previously asked on the internet. This is probably just a database question and not a spatial issue, but I am using spatial data.
UPDATE: I worked on it today (Sunday) some more. Created a new text field (let's call it 20) and populated by concatenating all the fields together successfully so that 'A.20 = A.2 & A.3 & A.4 & A.5 & A.6' 'B.20 = B.2 & B.3 & B.4 & B.5 & B.6' This seemed to work perfectly using only one field to match, except it returned only 2/3 of the expected records. Field 5 has high granularity and removing it from consideration returned 160% of the expected records. Does the "Make Query Table" tool have a limit to the number of fields it can access?