6 Replies Latest reply on Oct 31, 2016 2:18 AM by Wolter van Dam

    Many-to-many / Many-to-one relationships

    Wolter van Dam

      Hi everybody,

       

      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?

       

      Thx!

        • 1. Re: Many-to-many / Many-to-one relationships
          Norbert Maijoor

          Goedenavond Wolter,

           

          First question: Is this the situation you are encountering? At B you are referring to a n:n relationship that means in real life clients are getting services at different addresses and at the same time more clients can live at the same adress

           

          If that's the case. Be sure you have all required object "under the key" or with other words connect the different datasources with the right object. So clientinfo

          &  indicates should be connected with Client ID, ZIPCODE, adress.

           

          Just a check to start the discussion;)

          1 of 1 people found this helpful
          • 2. Re: Many-to-many / Many-to-one relationships
            Wolter van Dam

            Goedenmorgen Norbert;)

             

            I hope this screenshot helps.

             

            So in the first table 'Polygon' there are the different neigborhoods with several PointID's. In this example neighborhood BU_0001 has 5 pointID's to mark the area on a map. I want to join this table with the second table 'Utrecht' which contain all the zipcode / housenumber combinations within this area. So as you can see in this example BU_0001 has 3 different zipcode / housenumber combinations. Therefore the relationship beteen the first and second table should be many-to many

             

            To connect my third table 'clientinfo' with the 'Utrecht' table i use the unique zipcode / housennumber combinations. It is possible several clients live on the same adress as you can see in this example. Therefore this realtionshop should be one-to-many (i see in my first post i said many-to-many but it should be one-to-many).

             

            The last table 'indicaties' i will connect with the 'clientinfo' table based on clientID. Because a person has a unique number i can only use this field i guess.

             

            The problem i am encountering is that my data in the last last two tables gets duplicated by the number of PointID's in the first table.

             

            Example:

            When a neigborhood has 10 pointID's to mark that neighborhood on the map

             

            and in that neighborhood live 5 clients with together 12 products.

             

            Count ClientID (clientinfo) = 120

            Count ClientID (indicaties) = 120

            • 3. Re: Many-to-many / Many-to-one relationships
              Norbert Maijoor

              Goedemiddag Wolter,

               

              I don't have a SQL database but find my approach as reference below based on exelsheets and stored in attached workbook version 9.3

               

               

               

              • 4. Re: Many-to-many / Many-to-one relationships
                Wolter van Dam

                Hi Norbert,

                 

                Thanks a lot for the help! Sadly my problem still occurs in the model workbook you made.

                 

                What i'm trying to make is an interactive map with a parameter so i can check how many unique clients and products there are within a certain neigborhood. but the pointID's are messing up my data.

                 

                For example:

                In the model you made there are 6 clients whith 8 products. The thing is when i count products in for example a row calculation i get 36 products!!

                 

                Only when i use COUNTD i get the unique number of clients, but i am unable to use countd on my products row, then i still get the unique number of clients.

                • 5. Re: Many-to-many / Many-to-one relationships
                  Norbert Maijoor

                  Hi Wolter,

                   

                  Checked once again.  The join between Community Polygon should never been a inner but left outer off course;) Apologizes for that

                  Find attached adjusted workbook in version 10.1

                   

                  Prettig weekend gewenst.

                   

                   

                  • 6. Re: Many-to-many / Many-to-one relationships
                    Wolter van Dam

                    Hi Norbert,

                     

                    Even when the relationship between 'Utrecht' and 'Polygon' is a left-join instead of a inner join, my data still get's duplicated.

                    When you look at the attached workbook at the slide 'duplicated data'. The count of clientID should be 6 and the count of product should be 8 instead of both 37.

                     

                    Do you have another way to fix this problem. I was guessing custom SQL, but i never wrote my own queries.

                     

                    groet,

                     

                    Wolter