Mohammed Shafeek Mohammed Shafeek - 6 months ago 17
SQL Question

Mysql Join issue with self defined table

Here is my query. I am trying to join a table with a defined table. But the problem is when we are trying Left join it effects same like inner join.

SELECT A.*,B.*,
(CASE WHEN A.preday != '' THEN 'A' ELSE 'P' END) AS PreStat
FROM (SELECT '2016-05-04' AS preday
UNION
SELECT '2016-05-03'
UNION
SELECT '2016-05-02') AS A
LEFT JOIN `student_attendence` AS B ON B.date = A.preday
WHERE student_id='1' ;


For example in my right table doesn't contain a date field with value '2016-05-02' , But when we are trying left join it should come with NULL values ..but it won't come.

my right join table structure is
enter image description here

Please help me to solve it.

Answer

Think about this for a second. Student_ID only exists on student_Attendence table (for these 2 tables anway). A student may not have a record for that given day (must be the case if you're not getting 02 in the results; at least for student 1) If you want all days from your derived union table; then you need to filter students_ID before the join occurs so the unmatched day for student 1 record is preserved from the left join

SELECT A.*,B.*, (CASE WHEN A.preday != '' THEN 'A' ELSE 'P' END) AS PreStat 
FROM (SELECT '2016-05-04' AS preday 
      UNION SELECT '2016-05-03' 
      UNION SELECT '2016-05-02') AS A 
LEFT JOIN `student_attendence` AS B 
  ON B.date = A.preday 
  and student_id='1' ;

Student_Attendance for Student_ID 1 would be...

ID Date
1  2016-05-05
1  2016-05-04
1  2016-05-03
1  2016-05-01

So the left join would result in ...

Preday            ID Date                   
2016-05-04         1  2016-05-04
2016-05-03         1  2016-05-03
2016-05-02         

and when you apply the where clause ... since 2016-05-02 has no entry for Student 1, it gets eliminated..

Preday            ID Date                   
2016-05-04         1  2016-05-04
2016-05-03         1  2016-05-03

But if you move the where clause to the join... you get this as the filter is applied before the join, thus retaining the 02 date.

Preday            ID Date                   
2016-05-04         1  2016-05-04
2016-05-03         1  2016-05-03
2016-05-02         
Comments