Getting data from multiple tables using multiple queries on multiple tables

Question

I am creating attendance management system for a school in php and mysql. I have 3 tables in database these are:

1. Students
id 
student_name
class

2. subjects
id
subjects


3. attendances
id
subject_id
student_id
attend (boolean)
today_date

I am only storing students who are attending classes in attendance table.

I want grab the list of all students (which are in or not in attendance table) who are attend or not for a particular class, date, and subject.

Like;

Attendance for Class - X(Physics) on 18-08-2017

    ______________________________
    | Student Name  | Attendance |
    ------------------------------
    | Foo           |     0      |
    | Bar           |     1      |
    | Baz           |     0      |
    | Qux           |     1      |
    ------------------------------

I was trying queries

SELECT s.id,s.student_name, a.attend as attend 
FROM attendances as a 
INNER JOIN students as s on s.id = a.student_id 
WHERE s.class = 'X'
AND a.subject_id = 3 
AND a.date = '2017-08-18'

But I am not getting the result I want. I am sure there will be some better and simple way to achieve this which I am missing there, because I haven't use SQL in this way before.

rohit singh · Accepted Answer · 2017-08-17 19:01:41Z

1

Below query might help you to achieve desired result:

SELECT s.id,s.student_name, IFNULL(a.attend, 0) as attend FROM students as s LEFT JOIN attendances as a on s.id = a.student_id WHERE s.class = 'X' AND a.subject_id = 3 AND a.date = '2017-08-18'

answered Aug 17, 2017 at 19:01

rohit singh

372 bronze badges

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

3 Comments

Cody Over a year ago

Thanks, but this is giving only students which are available in attendances table, but I want all students which are available in attendances or not.

rohit singh Over a year ago

Use RIGHT JOIN.

Cody Over a year ago

I tried that but same result. I think it's because of these conditions "a.subject_id = 3 AND a.date = '2017-08-18'" it is giving this result. Is there any way to manipulate these conditions.

Deepak Kumar · Accepted Answer · 2017-08-18 14:09:39Z

1

SELECT s.id,s.student_name, a.attend as attend FROM attendances as a 
RIGHT JOIN students as s on s.id = a.student_id AND a.subject_Id=3 
AND a.date='2017-08-18' WHERE s.class = 'X'

edited Aug 18, 2017 at 14:09

answered Aug 18, 2017 at 13:28

Deepak Kumar

5512 bronze badges

2 Comments

dwirony Over a year ago

Please add formatting to your code (at least by highlighting your code and hitting Ctrl + K on your keyboard)

dwirony Over a year ago

No problem, I saw that this was your first answer and just wanted to help :)

Collectives™ on Stack Overflow

Getting data from multiple tables using multiple queries on multiple tables

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related