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. –  Geoist 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. –  Geoist Aug 9 '12 at 1:08

1 Answer 1

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. –  Geoist Aug 9 '12 at 1:37
    
@Fezter I've updated my answer, please have a look. –  Geoist 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.