Joining 2 Many-To-Many Relationship Tables

Question

Problem:

Find the net balance of the total order amount and total payments for each customer.

There are 4 tables involved: OrderDetails, Orders, Payments and Customer.

The total amount order = quantity order * price each [in OrderDetails]

The total payment = sum of different payments for the same order.

Customers are linked to Payments and Orders with CustomerNumber. Orders are linked to OrderDetails with OrderNumber.

I tried joining the 4 tables with the INNER JOIN function.

SELECT
    c.customername, 
    SUM(od.quantityordered * od.priceeach) - SUM(p.amount) AS Net_Balance
FROM 
    (
        (
            orderdetails od 
            INNER JOIN orders o ON od.ordernumber = o.ordernumber
        )  
        INNER JOIN customers c ON o.customernumber = c.customernumber
    )      
    INNER JOIN payments p ON c.customernumber = p.customernumber
GROUP BY c.customername;

The expected results should be 0 for almost every customers.

However, what I have got is the total amount order and total payment are multiplied by some constants. Specifically, the total payment shown is multiplied by the number of payments for each order.

Anybody have any ideas to save my life?

What happens is that you join order positions with customer payments. But all they have in common is the order they refer to. A single payment doesn't relate to a single order position. What you really want to do is join the customers' order totals (which you get by aggregating the orders' positions) with the customers' payments (which you get by aggregating the payments). See GMB's answer on this. — Thorsten Kettner, Mar 31 '19 at 20:51
Why do you delete most of your question's content after accepting the answer? I rolledback to the previous version. — GMB, Mar 31 '19 at 22:46
Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. (Debugging fundamental.) — philipxy, Mar 31 '19 at 23:33
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS This seems likely to be a common error where people want the join of some aggregations (each possibly involving joining) but they erroneously try to do all the joining then all the aggregating. PS Googe 'site:stackoverflow.com sql multiple aggregations are wrong'--many hits. — philipxy, Mar 31 '19 at 23:38
Possible duplicate of [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) — philipxy, Mar 31 '19 at 23:39

score 1 · Accepted Answer · answered Mar 31 '19 at 20:42

This is a typical issue when dealing with N-M relationships. To solve it, one solution is to move the aggregation to subqueries:

SELECT c.customername, o.amt - p.amt AS Net_Balance
FROM customers c 
INNER JOIN (
    SELECT ord.customernumber, SUM(det.quantityordered * det.priceeach) as amt
    FROM orders ord
    INNER JOIN orderdetails det ON ord.ordernumber = det.ordernumber
    GROUP BY ord.customernumber
) o ON o.customernumber = c.customernumber
INNER JOIN (
    SELECT customernumber, SUM(amount) as amt
    FROM payments
    GROUP BY customernumber
) p ON p.customernumber = c.customernumber

score 0 · Answer 2 · answered Mar 31 '19 at 20:54

0

SELECT c.customername, SUM(od.quantityordered*od.priceeach) as ordersum ,  SUM(p.amount) as paymentsum'

What's the result of the two columns ?
Is it what you want?

answered Mar 31 '19 at 20:54

Abdelrahman Maharek

802
5
19

Joining 2 Many-To-Many Relationship Tables

2 Answers2