CODEWITHSUNDEEP
sqlms-accessmany-to-manysubquery
SQL_Noob1010
SQL_Noob1010

Reputation: 35

Joining 2 Many-To-Many Relationship Tables

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?

Upvotes: 2

Views: 2375

Answers (2)

Abdelrahman Maharek
Abdelrahman Maharek

Reputation: 872

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?

Upvotes: 0

GMB
GMB

Reputation: 222632

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

Upvotes: 1

Related Questions