SQL Select query + advanced filter

[සශික]

I'm making money lending system. In my database there are two mysql tables which are custormer AND income. All custormer info stored in custormer table. When I give loans these loan details stored in loan table. After I received loan amount from custormers(daily payments) these records stored in income table.
What I want is,
Filter who are not give there daily installement today.
My tables are look like,
custormer table
custormer_id | custormer name
income table
custormer_id | loan_id | date

[hericles]

I think this will work (not tested). The important part is the inner select that returns all customer who DO have a record made against today's date and then you want to select the customers that are not in that list.

SELECT c.*, i.* FROM customer c 
JOIN income i ON c.customer_id = i.customer_id 
WHERE c.customer_id NOT IN (SELECT customer_id FROM income WHERE date = NOW())

[සශික]

Can't I put date value to line 3 NOW word ?

[hericles]

If you want but you implied you wanted today's transaction and NOW() gives you the current date. It's just simplier than passing in a variable you don't need.
If you wanted to check for a particular date then, yes, you can pass in whatever date you need.

[සශික]

bro I find solution. I replaced 'CURDATE' instead 'NOW' . There also one problem. The database also stored finished and not finish loan's data. I want to search and get results of person who not paid there installment 'curdate' and loan_status not finished loans.

Your code 100% right after and before I put 'curdate'. Can you help me this time on this problem ?

[hericles]

You didn't include the schema for the loan table in the initial post but all you need to do is include another join on the loan table. If the loan table has a customer ID (which I'm assuming it does), then turn the select statement into this:

SELECT c.*, i.*, loan.* FROM customer c 
JOIN income i ON c.customer_id = i.customer_id 
JOIN loan ON loan.customer_id = c.customer_id
WHERE c.customer_id NOT IN (SELECT customer_id FROM income WHERE date = CURDATE()) and loan.loan_status = <WHATEVER INDICATES UNPAID>