10

I'm trying to create what I call a "many to one" join. I don't know if that would be the correct term or not. I have a table that has unique account numbers for mobile homes (i.e. - M1007970) per parcel account number (R0003285). (Many mobile homes per parcel - many to one.) I need to join this table to our parcel geometry - and still only have one polygon per parcel.

So, for example, the table may have three rows that have mobile home account numbers M1007370 on one row, M1007371 on another, and another one with M1059370, but all have the same parcel number R0032585. Our parcel geometry would only have the same field of R0032585.

When joining I have 12,088 mobile home records and 44,103 parcels. If I "keep all records" I have 44,103 records with only 7,947 mobile home account numbers (of the original 12,088). If I join based on "keep only matching records" I end up with just 7,947 records total.

I have done it successfully in the past and created a model. In this model I use the table for the mobile homes to join to the parcel layer (.lyr - the only way you can/could join in a model) based on the parcel account number. I copy the features keeping only matching records to a file geodatabase. From the file geodatabase I then append it into our SDE system. This currently quit working for reasons that I can't fathom, as nothing has changed.

Any ideas would greatly be appreciated, especially if someone can convey better than I can what I'm trying to do, and if it's called something other than a many to one relationship (I don't believe it's a one to many...). A huge thank you in advance.

  • 1
    This might help. What about using relates instead of a join? – GISKid Aug 27 '13 at 19:39
  • So what I'm getting from that link is that my join should have never worked in the first place and I need to use a relate. The problem then, and maybe this is because I'm getting old and forgetting my education, how would I spatially portray the related table? – Evan Aug 27 '13 at 21:34
8

It's sometimes confusing, but it's really a matter of perspective. See this diagram (from this topic) for a reference:

Relationship diagram

This is an example of five relationships (and three different cardinalities).

  1. One-to-many: Parcels are related to the ParcelToOwner table in a one-to-many relationship; one parcel may have many owners (partial ownership).
  2. Many-to-one: The ParcelToOwner table is related to Parcels in a many-to-one relationship; many owners own (at least some percentage of) a parcel.
  3. Many-to-one: The ParcelToOwner table is related to Owners in a many-to-one relationship; many parcels may be owned (at least partially) by one owner.
  4. One-to-many: Owners are related to the ParcelToOwner table in a one-to-many relationship; one owner may own many parcels (again, at least partially)
  5. Many-to-many: Parcels are related to Owners in a many-to-many relationship; many parcels may be (at least partially) owned by many owners, and many owners may own (at least partially) many parcels. This is expressed through the ParcelToOwner table and the aforementioned relationships. Most DBMSs cannot express a many-to-many relationship without an intermediary table (source), hence this design.

As you can see, whether a relationship is one-to-many or many-to-one depends on how you look at it.

All that being said, the easiest way to accomplish what you're looking for is to create a query table that creates many identical parcels, one for each mobile home. See this blog post for more details: A quick tip on performing a 1:M join

If all of your data is in an enterprise geodatabase, you could also use a Query Layer to do the same thing, on the fly (no intermediate feature class).

  • 1
    +1 for Make Query Table, I use it all the time to spatially join asset registers containing thousands of records to single asset points, so that e.g. instead of a single pump station point, I now have 10 separate points displaying the pump, electrical board, motor info etc. – Cindy Jayakumar Aug 28 '13 at 5:20
  • Thank you for the answer, I've never used the Make Table Query before. It's always awesome to learn something new, it worked great. Thank you. – Evan Aug 28 '13 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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