MySQL multiple row joining

I have an issue with joining of tables that I have not managed to solve. Somehow I have the impression that it is more simple than I think. Anyhow:

I have three tables:

orders

orderlines

payments

In "orders" every line corresponds to one order made by a customer. Every order has an order_id which is the primary key for that table. In "orderlines" I keep the content of the order, that is references to the products and services on the order. One order can, and typically has, many orderlines. Finally, in payments I store one row for every transaction made to pay for an order.

One order ideally never has more than one corresponding row in payments. But since customers are customers it is not unusual that someone pays the same invoice twice, hinting that the payments table can have two or more payments for one order.

Therefore it would be useful to create a query that joins all three tables in a relevant way, but I have not managed to do so. For instance:

SELECT orders.order_id, SUM(orderlines.amount), SUM(payments.amount) FROM orders LEFT JOIN orderlines ON orders.order_id = orderlines.order_id LEFT JOIN payments ON orders.order_id = payments.o开发者_JAVA技巧rder_id GROUP BY orders.order_id

The purpose of this join is to find out if the SUM of the products on the order equals the SUM in payments. The problem here is that the two tables payments and orderlines "distract" each other by both causing multiple rows while joining.

Is there a simple solution to this problem?

Maybe I'm overcomplicating things, but using both tables and producing the sum would always lead too wrong results, i.e. one order has 10 orderline rows and 1 payment rows => the payment amount is going to be added 10 times. I guess you have to use subselects like this below (you didn't use anything from your table "orders" but the id, so I left it out, because all orders have orderlines):

SELECT t1.order_id, t1.OrderAmount, t2.PaymentAmount
FROM (SELECT SUM(amount) AS OrderAmount, order_id
      FROM orderlines
      GROUP BY order_id) AS t1
LEFT JOIN (SELECT SUM(amount) AS PaymentAmount, order_id
        FROM payments
        GROUP BY order_id) AS t2 
        ON t1.order_id=t2.order_id

I think what you want to do is get the sum of all the items, and the sum of all the payments, and then link them together. A sub-select is able to do this.

Something like: (ps I have no database on hand so it might not be valid sql)

SELECT * FROM orders
LEFT JOIN (SELECT order_id, SUM(amount) FROM orderlines GROUP BY order_id) AS ordersums
ON orders.order_id = ordersums.order_id
LEFT JOIN (SELECT order_id, SUM(amount) FROM payments GROUP BY order_id) AS paymentsums
ON orders.order_id = paymentsums.order_id;