5 Replies Latest reply on Jun 23, 2011 8:03 PM by Joe Mako

    Performing the right join to not duplicate unique data

    Drew Graham

      I have a database called payment that looks like the following:

       

      PaymentID,DetailID,PaymentType,PaymentAmount

      p1,t1,cash,100

      p2,t2,card,75

       

      Under a detail table I have:

       

      DetailID,ProductID,Qty,Price,Amount

      t1,i1,10,5,50

      t1,i2,10,5,50

      t2,i3,1,75,75

       

      When I join the tables payment and detail, I set the join clause to be DetailID.  However, under View Data the table then looks like:

       

      PaymentID,DetailID,ProductID,Qty,Price,Amount,PaymentType,PaymentAmount

      p1,t1,i1,10,5,50,cash,100

      p1,t1,i2,10,5,50,cash,100

      etc etc

       

      Now, the sum of Amount is equal to $100 (correct) but the sum of PaymentAmount is equal to $200 (incorrect) because there are two DetailID items in the single PaymentID

       

      The issue is compounded when I add in a ingredient table that breaks the ProductID down in to ingredients - now the sum of Amount is multiplied by the number of ingredients!

       

      I'm obviously doing something wrong / silly here, as this must be a really common issue when taking a relational database and joining it to form a single table - can anyone show me what please?

       

      Maybe redoing the joins, figuring out a calculated field to get the right (distinct / unique / unrepeated) data.

       

      Thank you.