Table customers
id fName lName pNumber
1 Adeline Brown 55512300
2 May Green 55512094
Table accounting
id customerName datePaid amount
1 Brown, Adeline 2012-08-09 210
2 Green, May 2012-09-09 430
My problem is how do I select the pNumber based on the datePaid corresponding to customerName?
You should remove the customerName from the Accounting table, and replace it with the CustomerId. But, for now, you can JOIN the two tables using any predicate as the JOIN condition, something like this:
SELECT c.pNumber
FROM Customers c
INNER JOIN accounting ac ON c.fName + ', ' + p.lName = ac.customerName
The problem with your table Accouting structure, is that, it isn't normalized, especially, doesn't meet the third normal form 3NF which is:
3NF: Eliminate Columns not dependant on the key
The column customerName in the Accounting table doesn't depend on the accounting id of that table. So, your tables should look like:
Customers table:
id, fName, lName, pNumber.accounting table:
id, customerId foreign key references Customers(Id), datePaid, amount.Then you can JOIN the two tables directly ON customerId which will improve the performance very much:
SELECT c.pNumber
FROM Customers c
INNER JOIN accounting ac ON c.Id = ac.CustomerId
Edit: Nothing wrong in your query. May be you haven't any rows that match this condition. You can try the following query it is the same as what you wrote, but it more organized:
SELECT c.pNumber
FROM customers c
INNER JOIN
(
SELECT DISTINCT id, customerName, datePaid, amount
TO_DAYS(DATE(datePaid)) - TO_DAYS(CURDATE()) AS DaysFromPayment
FROM Accounting
) ac ON c.last_name + ', ' + c.first_name = ac.customerName
WHERE ac.DaysFromPayment = 30