10 Replies Latest reply on Jan 3, 2015 1:26 PM by xander_bakker

    Joining based on multiple fields

    MidnightYell2003
      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
        • Re: Joining based on multiple fields
          johns
          Concatenate your two fields values into a new field and join on that.
          • Re: Joining based on multiple fields
            MidnightYell2003
            I was afraid that this might be the answer.  Is there not a work around for this?
            • Re: Joining based on multiple fields
              johns
              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.
              • Re: Joining based on multiple fields
                jguetter97
                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.
                • Re: Joining based on multiple fields
                  rfairhur24
                  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).
                  • Re: Joining based on multiple fields
                    Irudych8

                    Did anyone actually solved this problem?

                    Concatenating fields wouldnt work for me as i'm working with my clients database and dont have writing permissions. I would like to join my clients data to my geodatabase and serve it out as a web service. But i have to join it based on multiple attributes. I have a sample site number and a date sample was taken. Both tables have same attributes. Why cant i just simply join them?

                      • Re: Joining based on multiple fields
                        rfairhur24

                        The answer is no, there is no solution without creating concatenated fields or a new output.  Standard joins simply do not permit you to use more than one field to establish the join, so it is impossible to use that as an option.  Joined on only one field I am sure you have a One to Many or Many to Many relationship, which also is also not resolved by a standard join without creating a new feature class.

                         

                        A python dictionary and cursor routine can do the concatenation on the fly without creating an actual concatenated field to match the records, but you would have to create a new feature class output in a workspace you can write to with no live connection to the source data.  That script would do the transfer quicker as a new feature class than any join or Query Table and the resulting feature class would redraw much faster and query much faster.  The data sources also do not need to all exist in the same workspace to apply this approach, but the output workspace for the new feature class would have to be writable.  Are you interested in that code?

                        1 of 1 people found this helpful
                      • Re: Joining based on multiple fields
                        jbrod

                        There are a couple of possible options depending on data source, permissions, etc. The first would be creating a query layer using a join. The type of join would depend on what type of results you're looking for. If you are pulling the data from one database and you have permission, you could create a  database view. Not sure if these will work in your case but they're worth looking into.