CODEWITHSUNDEEP
phpmysql
Ash
Ash

Reputation: 85

left join not working in php

I have a 'student' table and 'attendance' table If a student is absent I just enter student ID and date in attendance table. Now I want to retrieve all students and their attendance on a particular date However when I am trying left Join, it gives me data of a particular student absent on that particular date

Table 'student' structure and sample data:

id|name (varchar field)
1 |xxx
6 |yyy

Table 'attendance' structure and sample data:

id|date (date field)|student_id (integer field)
1 |2015-10-15       | 1
1 |2015-10-16       | 6

My query

SELECT *
FROM student.id, student.name, attendance.date
  LEFT JOIN attendance
    ON student.id = attendance.student_id
WHERE attendance.date = '2015-10-15'

The output is

1   xxx 2015-10-15

However required output is

1   xxx 2015-10-15
6   yyy NULL (or a blank)

Upvotes: 1

Views: 1112

Answers (2)

dotnetom
dotnetom

Reputation: 24901

For the output you desire you need to move your condition attendance.date = '2015-10-15' from WHERE clause, to ON clause:

SELECT student.id, student.name, attendance.date
FROM student
  LEFT JOIN attendance
    ON student.id = attendance.student_id AND attendance.date = '2015-10-15'

In this case date will be used during the join. If you use it in WHERE clause, the filtering is done after data is joined, so the second line from your desired output is filtered out

Here is a SQL Fiddle demonstrating the solution

Upvotes: 2

Chris
Chris

Reputation: 4810

You are missing the primary table. You need to move the FROM to the end of the SELECT portion, then actually name a table

SELECT *, student.id, student.name, attendance.date
FROM student
  LEFT JOIN attendance
    ON student.id = attendance.student_id
WHERE attendance.date = '2015-10-15'

Upvotes: 0

Related Questions