It is necessary to pick up all students who did not pay for the current month
I have two tables
Student table:
id studentname admissionno
3 test3 3
2 test2 2
1 test 1
The second table is the fee:
id studentid created
1 3 2015-06-06 22:55:34
2 2 2015-05-07 13:32:48
3 1 2015-06-07 17:47:46
I need to pick up students who have not paid for the current month,
I run the following request:
SELECT studentname FROM students
WHERE studentname != (select students.studentname from students
JOIN submit_fee
ON (students.id=submit_fee.studentid)
WHERE MONTH(CURDATE()) = MONTH(submit_fee.created)) ;
and I get the error:
'# 1242 - Subquery returns more than 1 row'
Can you tell me what is the correct query for all students who have not paid for the current month?
source to share
Do you want to use not exists
or left join
for this:
select s.*
from students s
where not exists (select 1
from studentfees sf
where s.id = sf.studentid and
sf.created >= date_sub(curdate(), interval day(curdate) - 1) and
sf.created < date_add(date_sub(curdate(), interval day(curdate) - 1), 1 month)
)
Pay attention to the careful construction of the date arithmetic. All functions are on curdate()
, not on created
. This allows MySQL to use the index for the sentence where
if applicable. One mistake in your request is using MONTH()
without using YEAR()
. In general, they will usually be used together unless you really want to mix months from different years.
Also note that payment or default for the current month may not answer the question. What if a student paid for the current month but missed the payment for the previous month?
source to share