I'm trying to join different tables, 4 to be precise, on different aggregation levels:
- The first table (Polygon) contains polygon and point'ID's to plot neighborhoods and areas within a map. Each neighborhood has it's own neigborhood code.
- The second table named 'Utrecht' contains all adresses (zipcode and housenumber) within a neigborhood. This table also contains the neighborhood code.
- The third table 'clientinfo' contains all inhabitants who are registered as a cliënt. This table contains all zipcodes and housenumbers and clientID's
- The fourth table 'indicaties' contains all the different products inhabitants recieve. this table contains all clientID's and product information
Now i'm trying to join those different tables but a lot of my data gets duplicated. I want to be able to see how many inhabitants are a client within a cetrain neighborhood and how many products they recieve.
To do that i want to create the following joins:
Polygon A: (n:n) Utrecht B: (n:n) clientinfo C: (1:n) indicaties
A: A certain neigborhood has several Point ID's which i want to connect with the table 'Utrecht' where a neighborhood has several Zipcode / housenumber combinations. Here i should get a many-to-many relationship.
B: I want to connect the file 'Utrecht' with clientinfo based on zipcode and housenumber combinations. It is possible several clients live on the same adres thats why i want to have a many to many relationship.
C: A unique client can have several products. I want to connect clientinfo with indicaties based on clientID.
PS: The biggest problem i have is that my data get duplicated. For example: i have 1556 unique products. But right now this gets duplicated by the pointID's of the first table. So right now he says there are 16.000+ products.
I tried working with a legacy connection and working with a SQL data base (witch make the joins more flexible). But i still haven't fixed this problem. Anyone any thoughts?