left join not working in php

Question

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)

Whenever you use Where clause in query it will return only data which satisfies where condition, So you need to use that condition in ON instead of WHERE — Vinie
– Vinie, Commented Oct 23, 2015 at 5:40

krivtom · Accepted Answer · 2015-10-23 03:44:41Z

2

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

edited Oct 23, 2015 at 3:44

answered Oct 23, 2015 at 3:37

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ash Over a year ago

Perfect. Many Thanks.

Chris · Accepted Answer · 2015-10-23 03:39:00Z

0

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'

answered Oct 23, 2015 at 3:39

Chris

4,8304 gold badges48 silver badges83 bronze badges

1 Comment

Chris Over a year ago

Also, you should reconsider the SELECT statement. You are selecting *, which may throw an ambiguous problem error, but also selecting specific items from each table.

Collectives™ on Stack Overflow

left join not working in php

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related