+ Reply to Thread
Results 1 to 6 of 6

Thread: Joining based on multiple fields

  1. #1
    Adrian Welsh
    Join Date
    Apr 2010
    Posts
    19
    Points
    0
    Answers Provided
    0


    0

    Question Joining based on multiple fields

    I searched around for this but could not find an easy answer. I have a GIS layer that I want to join a table to. The layer and the table each have 2 unique fields that relate together. I would like to be able to do this manually or programmatically (just want it to work for now).

    Thanks,
    Adrian

  2. #2
    John Sobetzer

    Join Date
    Apr 2010
    Posts
    1,007
    Points
    240
    Answers Provided
    25


    0

    Default Re: Joining based on multiple fields

    Concatenate your two fields values into a new field and join on that.

  3. #3
    Adrian Welsh
    Join Date
    Apr 2010
    Posts
    19
    Points
    0
    Answers Provided
    0


    0

    Lightbulb Re: Joining based on multiple fields

    I was afraid that this might be the answer. Is there not a work around for this?

  4. #4
    John Sobetzer

    Join Date
    Apr 2010
    Posts
    1,007
    Points
    240
    Answers Provided
    25


    0

    Default Re: Joining based on multiple fields

    I can think of a couple other but they are clumsier. (Someone smarter than me might be able to help you but this is the common answer I've seen on the Forums.) You could sort on your unique fields in each program, autonumber and then join based on the autonumber. You could export your table with a unique field to another program, like Access, and then do the joins there and join that back using the unique field. If you are using a shapefile, and you've backed it up, you could open up the dbf in Access, do the joins and replace the dbf with it. Don't change the order.

  5. #5
    John Guetterman
    Join Date
    May 2012
    Posts
    3
    Points
    2
    Answers Provided
    0


    1

    Default Re: Joining based on multiple fields

    I think I figured this out. Use the "Make Query Table" tool and input the two tables you want to join. In the SQL Expression box, enter something like:
    (tbl1.field1 = tbl2.field1) AND (tbl1.field2 = tbl2.field2)

    When I hit "Verify" in the SQL builder dialog, it tells me there is an error, but despite that, it still works. In my test, it produced an in-memory table containing all the fields from both tables and it properly joined the tables via the combination of field1 & field2.

  6. #6
    Richard Fairhurst

    Join Date
    Apr 2010
    Posts
    2,179
    Points
    1372
    Answers Provided
    195


    1

    Default Re: Joining based on multiple fields

    Quote Originally Posted by jguetter97 View Post
    I think I figured this out. Use the "Make Query Table" tool and input the two tables you want to join. In the SQL Expression box, enter something like:
    (tbl1.field1 = tbl2.field1) AND (tbl1.field2 = tbl2.field2)

    When I hit "Verify" in the SQL builder dialog, it tells me there is an error, but despite that, it still works. In my test, it produced an in-memory table containing all the fields from both tables and it properly joined the tables via the combination of field1 & field2.
    This is the only solution to the problem as far as ArcMap supports. However, there are several limitations to a Make Query Table output. First, only records with matches show up in the table. It is not possible to do a keep all parent table records like you can with a normal join. Therefore any records that have no relationship between the two tables cannot be viewed in the Make Query Table output.

    Second, the Query Table cannot be directly edited and it does not really respond to edits in the origin tables either without a complete regeneration. So if responsive changes to edits is what you want to see, this won't do that.

    However, you can export the table to create a real table in a geodatabase and then use that to edit features. But it won' respond to edits in the original tables at all. If the exported result retains a unique ID value from each origin table, you can use a regular join to transfer attributes (assuming each match is 1:1, or M:1 back to your origin table, but not 1:M or M:M).
    If your question was answered don't forget to click the Mark as answer check and to click the top arrow (promote) at the right hand side of the post.

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts