Creating non-spatial Many to One Joins/Relationships between tables

Question

I may have missed a trick here but I can't seem to find a solution.

I have a table of ~1.6 million postcodes, also containing co-ordinates, and a separate file detailing ~6000 a population with postcodes attached. I am trying to attach the co-ordinates from the postcode file to the population file. There is, but not always, two or more individuals associated to one postcode. The theory, in my head, is simple. Join the postcode table to the population table on the postcode attribute. To start with this idea in ArcGIS 10.1 seemed simple, using a basic join, but what seems to be happening is that a 1:1 relationship is being enforced. After researching further I found the Make Query Table tool which claimed to solve this M:1 problem. Though that doesn't seem to be working. I am currently in a two-step process to see if I can solve this. First is looking through some, don't particularly want to look through all files in either table, to see if there is a formatting error that would explain this error. The second is looking into using MySQL to see if that can be used to join the tables. Or at least create a query of the two tables that will produce the desired output table.

First off if you have any advice on making this work in ArcGIS that'd be brilliant, I'm sure it can do it, just not sure how to make it work. Alternatively if you know of another method that would easily join these tables, be that QGIS, MySQL or something else, then please do say.

Hope you can help, and if there's anything I've missed out that would help you understand my problem, let me know.

Answer 1

There is some useful online documentation that recommends using a relate or relationship class for one-to-many (and many-to-many) because ...

In all cases of 1:M joins, only the first matching record is joined and displayed in the layer's attribute table.

Answer 2

This is possible in ArcGIS by creating a spatial view or query layer of your table with the columns for post codes and the shape ( coordinates) and joining that to your population table using SQL join and matching the post codes column to your spatial view. This will essentially give you post codes and coordinates for every population matched. You can also do this in a regular SQL environment like MS SQL or any of the other spatial RDBMS. Esri has help docs about spatial views if you do a little searching from resources.arcgis.com and pick ArcGIS desktop help. The syntax should be something like this: Select a.postcodes, a.shape, b.population (and whatever other columns) From postcodes table a join on population table b Where a.postcodes = b.postcodes