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?

Matt Glover · Accepted Answer · 2012-02-12T04:33:38.103

2

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;

edited Feb 12 '12 at 04:33

answered Feb 12 '12 at 04:26

Matt Glover

1,347
7
13

Your absolutely right about c.customerName, that was a typo it should be c.customerNumber instead. I am trying your implementation now – Warz Feb 12 '12 at 04:32

John Woo · Answer 2 · 2012-02-12T04:33:43.257

Basically you missed the WHERE clause. And your question lacks information. Please provide the Schema of your tables. Thanks!

try this one:

Are you sure with this condition ON c.customerName = o.customerNumber?

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

OR Without Subquery

SELECT a.*
FROM Customers a INNER JOIN Orders b ON
        a.CustomerName = b.CustomerNumber -- Please check this line
     LEFT JOIN Payments c ON
        b.CustomerNumber = c.CustomerNumber
WHERE c.CustomerNumber IS NULL

I believe it's a typo error on a.CustomerName = b.CustomerNumber, instead a.CustomerNumber = b.CustomerNumber

I can't tell exactly if it is because you didn't provide the schema of your tables with some dummy records.

Hope this helps.

i tried your second solution, does it what it's suppose to. upvote for the help. — Warz, Feb 12 '12 at 04:37

score 0 · Answer 3 · answered Feb 12 '12 at 05:09

Unlike the other solutions, this solution will not produce duplicate customer numbers when customers have more than one order.

SELECT C.customerNumber 
FROM ClassicModels.Customers C 
WHERE 
EXISTS(
    -- customer has orders
    SELECT * 
    FROM ClassicModels.Orders AS O
    WHERE O.customerNumber = C.customerNumber 
)
AND NOT EXISTS(
    -- customer does not have payments
    SELECT *
    FROM ClassicModels.Payments P
    WHERE P.customerNumber = C.customerNumber
)

Mysql Query: Find Customer W/Orders but without Payment

3 Answers3