optional where clause condition in mysql

rupinder18 Published at Dev

rupinder18

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

M Khalid Junaid

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'

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-7

Comments

0 comments

From Dev

Related Related

Article

optional where clause condition in mysql

optional where clause condition in mysql

Make an optional Where Clause in mysql

Right way to use optional condition in WHERE clause

ignoring condition in where clause in mysql

mysql query apply condition to where clause only if certain condition satisfied

MySQL : How to get latest row on optional where clause using group by?

Using "If" condition in where clause

Condition in WHERE clause (Oracle)

SQL : If condition in where clause

select Condition in where clause

MySQL UNION where clause condition from the first select

PHP MySQL PDO TextArea Where clause with condition checks

MYSQL: How to use the result of a grouped SUM() as condition in WHERE clause?

optional where clause jasper reports

Sequelize optional where clause parameters?

optional where clause jasper reports

INNER JOIN condition in WHERE clause or ON clause?

SQL Query: difference in condition in On clause and where clause

Use Condition In Where Clause In Linq

LINQ: IF condition in WHERE clause not working

MongoDB multiple condition in WHERE clause

SQL Server : IF Condition in WHERE clause

Variable condition in LINQ where clause

SQL WHERE Clause condition evaluation

AND condition in WHERE clause on multiple columns

IF ELSE CONDITION IN SQL WHERE CLAUSE

Use Condition In Where Clause In Linq

MongoDB multiple condition in WHERE clause

Checking condition in IF vs WHERE clause

LINQ: IF condition in WHERE clause not working