CODEWITHSUNDEEP
mysqlsqldatejoinleft-join
Bilal Zahid
Bilal Zahid

Reputation: 31

Null Condition with current month condition

i am trying to run a mysql select query where i want to fetch the students who did not pay the fee at current month. please check my condition if i use these one by one it works but they both at same time dont work

"SELECT A.studentid,A.stu_first_name,A.stu_last_name,
 A.stu_reg_no,A.phone,
 B.submit_on,
 C.class_name FROM students as A 
 LEFT JOIN student_fee as B on A.studentid = B.student_id 
 inner join class as C on 
 A.class_id=C.classid 
 WHERE MONTH(B.submit_on) = MONTH(NOW()) 
 AND B.student_id IS NULL"

Upvotes: 1

Views: 35

Answers (1)

GMB
GMB

Reputation: 222432

You would need to move the condition of B.submit_on from the WHERE clause to the LEFT JOIN on table B. Otherwise, records where there is no match in B are filtered out by the WHERE clause, which I believe is the opposite of what you want (this actually turns your LEFT JOIN into an INNER JOIN).

SELECT 
    A.studentid,
    A.stu_first_name,
    A.stu_last_name,
    A.stu_reg_no,
    A.phone,
    B.submit_on,
    C.class_name
FROM 
    students as A 
    LEFT JOIN student_fee as B on A.studentid = B.student_id AND MONTH(B.submit_on) = MONTH(NOW()) 
    INNER JOIN class as C ON A.class_id = C.classid 
WHERE B.student_id IS NULL"

Upvotes: 1

Related Questions