CODEWITHSUNDEEP
c#mysqlasp.net
rupinder18
rupinder18

Reputation: 815

optional where clause condition in mysql

I am working on a project where I load a list of students depending on the date selected and the teacher chosen from a dropdown.The list contains those students data whose class teacher is the teacher selected. Now i am using the following query:

select s.studentcode,s.studentname,a.attdate,if(a.period='01' or 
a.period='AM',a.status,'') as attAM,if(a.period='PM',a.status,'') as attPM 
from tblstudent s left join tblattendance a on s.studentcode=a.studentcode 
where s.classcode='10002' and a.attdate='2014-04-11'

Now the problem with the above is query is that if i chose some date like 2014-04-15 then as attendance is not marked for this date then no record is found,but i want that the student list is always displayed and if attendance is marked for that date it should be displayed accordingly else those fields will be blank

enter image description here

The above screenshot is displaying the list which I want.Now the field AM should be blank if attendance for that date is not marked and if it is marked it should contain the corresponding attendance

Upvotes: 1

Views: 569

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Move your AND a.attdate = '2014-04-11' to join's ON() clause so if date is not present it still returns the records for s.classcode = '10002' and a.attdate column will be null in the result set,where filter is applied to whole resultset where as using additional condition in join will filter the records from right table i.e tblattendance not on whole resultset

SELECT 
  s.studentcode,
  s.studentname,
  a.attdate,
  IF(
    a.period = '01' 
    OR a.period = 'AM',
    a.status,
    ''
  ) AS attAM,
  IF(a.period = 'PM', a.status, '') AS attPM 
FROM
  tblstudent s 
  LEFT JOIN tblattendance a 
    ON (s.studentcode = a.studentcode AND a.attdate = '2014-04-11')
WHERE s.classcode = '10002'

Upvotes: 1

Related Questions