CODEWITHSUNDEEP
mysqlsql
Mohammedshafeek C S
Mohammedshafeek C S

Reputation: 1943

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.

Upvotes: 0

Views: 56

Answers (2)

Wael Mourad
Wael Mourad

Reputation: 164

the reason because of the logical query processing of the query as the first clause to be executed is the from clause then where > grouping > having > select > order by

so when you execute your own query the first clause to be executed is the from which returned '2016-05-02' with null as expected with the left join but the where clause after that filters the result set and remove it because the null is unknown (it isn't a value, it stands for missing value)

so you can do something like replacing the where clause with and operator.

Upvotes: 0

xQbert
xQbert

Reputation: 35333

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

Upvotes: 1

Related Questions