Mysql Query: Find Customer W/Orders but without Payment

Question

As the title says, i am trying to find the customer's who have made orders but have not made payments yet.

I have Three tables;

Customers, Payments, Orders

The sql i have so far gives me (nested query) all the customers without payments, the outer query then tries to join all the customers with orders and checks if those customers are not in my inner table?

SELECT customerWOpayments.customerNumber FROM 
ClassicModels.Customers c
INNER JOIN ClassicModels.Orders o ON c.customerName = o.customerNumber
NOT IN 
(SELECT  distinct c.customerNumber
FROM ClassicModels.Customers c
LEFT OUTER JOIN ClassicModels.Payments p ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL) customerWOpayments;

I am getting a mysql syntax error at line 8 but cannot figure out why?

Answer 1

This should return customers who have orders but no matching payment assuming all of the keys you joined on in your original example were correct (for example c.customerName = o.customerNumber seems suspicious).

SELECT c.customerNumber
FROM ClassicModels.Customers c
  INNER JOIN ClassicModels.Orders o
    ON c.customerNumber = o.customerNumber
  LEFT OUTER JOIN ClassicModels.Payments p
    ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL;