Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have two feature classes in a File Geodatabase that I would like to join based on multiple fields. I've searched this site and Google and all I have come up with was to use the Make Query Table tool. I've tried this, but I keep getting an SQL error. My SQL is pretty poor and I'm pretty sure I'm missing something.

I am aware I can create a new field and concatenate the values from my fields, but I would like to avoid this, if possible.

I'm using something that looks like this:

(Table1.Field1 = Table2.Field1) AND (Table1.Field2 = Table2.Field2) AND (Table1.Field3 = Table2.Field3)

When I verify the query, I get an error that says:

There was an error with the expression. An Invalid SQL statement was used. An invalid SQL statement was used. [Table1]

Also, if someone has another solution that doesn't use this tool, I'm happy to hear about it.

Thanks.

share|improve this question
    
Try joining Table 1 to Table 2, then joining Table 3 to the result of that. It would also help you if you used query builder ([sql] button in the Make Query Table tool) to verify the syntax. –  nagytech Aug 9 '12 at 0:16
    
Note, there are only two tables but three fields. I am using query builder and the error I get appears when I click on Verify. The join is effectively joining fields 1,2,3 from table 1 to fields 1,2,3 from table 2. –  Fezter Aug 9 '12 at 0:18
    
I didn't read the post very well, it makes more sense now. –  nagytech Aug 9 '12 at 1:08

3 Answers 3

Just as a reminder: also make sure the classes or tables are part of the same geodatabase AND make sure the keyfields on which the join will be operated are of the same type.

share|improve this answer

(FGDB 10.2 & ArcMAP 10.2) Perhaps opening up an old question - but I think this is relevant. The Make Query Table SQL creator throws an error when you verify, even if your syntax is correct. I created my query without any double-quotes or brackets, simply

Table1.Field1 = Table2.Field1 AND Table1.Field2 = Table2.Field2

and did not select verify; I simply ran the tool and my output table was correctly created.

Another Stackexchange question referencing this error; Review User2118's answer

share|improve this answer
2  
I ran into the same thing with a FGDB. I ran the tool once with quotes ("table.field" = "table2.field") like the Expression Builder created, and the Verify and the tool failed. I removed the quotes (table.field = table2.field) and the verify failed, but the tool ran successfully. Both ArcGIS Desktop and the FGDB are 10.2.2. –  Dan Sep 23 at 17:01

Your syntax is incorrect.

Reference

If you read the Make Query Table sidebar help:

Expression (optional)

An SQL expression used to select a subset of records. The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBase, or INFO tables, enclose field names in double quotes:

"MY_FIELD"

If you're querying personal geodatabases, enclose fields in square brackets:

[MY_FIELD]

etc...

That would imply the correct format as:

"Table2"."f1" = "Table1"."f1" AND "Table2"."f2" = "Table1"."f2" AND "Table2"."f3" = "Table1"."f3" 

Script

The following python snippet will enable you to run the tool without error:

arcpy.MakeQueryTable_management("Table1;Table2","QueryTable11111","NO_KEY_FIELD","#","Table1.f1 #;Table1.f2 #;Table1.f3 #;Table2.f1 #;Table2.f2 #;Table2.f3 #","/\Table1/\./\f1/\ = /\Table2/\./\f1/\ AND /\Table1/\./\f2/\ = /\Table2/\./\f2/\ AND /\Table1/\./\f3/\ = /\Table2/\./\f3/\")

Result

enter image description here

I hope this helps.

share|improve this answer
    
I am aware of this method, as I mentioned in my question. I'm looking for another way, if there is one. –  Fezter Aug 9 '12 at 1:26
1  
Although you are aware of the solution, and is, in fact, a solution to your problem, others may not be. –  nagytech Aug 9 '12 at 1:37
    
@Fezter I've updated my answer, please have a look. –  nagytech Aug 9 '12 at 2:00
    
thanks for the updated answer. I used the query builder, so I assumed it would automatically put it in the correct syntax for me. I added the quotes and it did get a bit further than before. I'm able to run the tool but I get the following error now: ERROR 999999: Error executing function. An expected Field was not found or could not be retrieved properly. [QueryTable] An expected Field was not found or could not be retrieved properly. [Table2.Field1] An expected Field was not found or could not be retrieved properly. [Table2.Field1] Failed to execute (MakeQueryTable). –  Fezter Aug 9 '12 at 2:10
1  
I've taken back the downvote for your effort. Much appreciated. –  Fezter Aug 9 '12 at 2:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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