Make Query Table - anthonyblackham/GIS-Wiki GitHub Wiki
When dealing with cardinality (type of data relationship: one to one, one to many, etc.) there are many ways to manage your data. You can do joins which are typically one to one or many to one (which may only pick the first match in the table) but if you need to do a one to many or many to many than you will either need to make a relationship class to handle the relationships or make a query table which will duplicate the features.
Looking at a real world example, I have a a database of documents that apply to many different properties. Let's quickly map out the cardinality of the data:
- Some documents only apply to one property
- Some documents apply to many properties
- Some properties have many documents
- Some properties have one document
I know this sounds a little redundant but we have to look at the relationships from both sides of the table. I have two main tables, one for my documents, one for my properties and I've created an intermediate table which shows which documents apply to which properties. EG:
(1) Document Table:
| OBJECTID | DOCUMENT |
|---|---|
| 1 | doc1 |
| 2 | doc2 |
| 3 | doc3 |
| 4 | doc4 |
(2) Property Table:
| OBJECTID | PARCELNUM |
|---|---|
| 1 | 3600 |
| 2 | 3700 |
| 3 | 3800 |
| 4 | 3900 |
(3) Intermediate Table:
| OBJECTID | PARCELNUM | DOCUMENT |
|---|---|---|
| 1 | 3600 | doc1 |
| 2 | 3700 | doc1 |
| 3 | 3800 | doc3 |
| 4 | 3800 | doc4 |
| 5 | 3900 | doc5 |
From Table (1) to Table (2) you can see doc1 applies to two properties (one to many) but also property 3800 has two documents, doc3 and doc4 (many to one) and of course there are also properties with just one document (one to one). Because both sides of the table have a one to many or many to one relationship, if we wish to retain all the information from both tables we need to set up a many to many relationship. Typically this would be done with a relationship class but for this exercise instead of creating a relationship class we are going to create a query layer that will duplicate the features instead while retaining the relevant attributes.
Catalog>>System Toolboxes>>Data Management Tools>>Layers and Table Views>>Make Query Layer(or you can just search for Make Query Layer, its faster)
You'll notice that the expression field doesn't have quotation marks like all the examples on ESRI. For some reason the expression only works if it DOESN'T have quotation marks. Also make sure the layers are in the same file geodatabase.
This is the original properties layer:
This is the query Table Layer, notice how property 3800 was duplicated (due to it having two different documents on that property):
