根据订单ID获取所有客户付款

Question

I wonder if somebody could help me with this complicated mysql query.

I have 3 tables:

Table #1: Customer_Order that have two columns customer_id and order_id
Table #2: Order_Payment that also has order_id
Table #3: Orders that has an id which is an order id.

I was wandering if it was possible to retrieve all payments from a specific customer based on order_id. I only have an order_id but I don't have the customer_id and based on the order_id, I should be able retrieve all the payments and even those that have not been paid yet.

This is how I do it:

I first retrieve the order which is known

select * from customer_order where order_id = 1351

then I try to build the query based on what is returned

select * from (
    select * from customer_order where order_id = 1351
) c1
left join 
    (select op.order_id, customer_id from order_payment op inner join customer_order co on op.order_id = co.order_id
    ) c2 on c1.customer_id = c2.customer_id

I also try

select customer_id from (
    select customer_id from `order_payment` inner join `customer_order` on `customer_order`.`order_id` = `order_payment`.`order_id` 
    where order_payment.order_id=1351
    order by `created_at` asc) as s
inner join customers on s.customer_id = customers.id

but I just get only one order payment and not those that have not been paid.

I would appreciate If someone out there would help me with the solution to the problem.

Answer 1

Something like this should work:

SELECT 
    co.*,
    op.*
FROM
    customer_order AS co
    LEFT JOIN order_payment AS op ON op.order_id = co.order_id

WHERE
    co.customer_id IN 
    (
         SELECT customer_id FROM customer_orders WHERE order_id=1351
    )