CODEWITHSUNDEEP
mysql
user
user

Reputation: 33

mysql - find all students of a class if at least one student attended

I have four tables to map students and classes they attend, and to keep attendance info with start and end times.

Reproducible table schemas with records:

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

INSERT INTO `student` (`id`, `name`) VALUES
(1, 'student 1'),
(2, 'student 2'),
(3, 'student 3'),
(4, 'student 4'),
(5, 'student 5');


CREATE TABLE IF NOT EXISTS `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

INSERT INTO `class` (`id`, `name`) VALUES
(1, 'class 1'),
(2, 'class 2');


CREATE TABLE IF NOT EXISTS `student_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
  FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
)ENGINE=InnoDB;

INSERT INTO `student_class` (`id`, `student_id`, `class_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 4, 2),
(5, 5, 2);


CREATE TABLE IF NOT EXISTS `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_class_id` int(11) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`student_class_id`) REFERENCES `student_class` (`id`)
)ENGINE=InnoDB;

INSERT INTO `attendance` (`id`, `student_class_id`, `start_time`, `end_time`) VALUES
(1, 1, '09:00:00', '10:00:00');

Problem:

I need to list down rows which only shows all attendance of a class to which at least one student attended, (even if the rest of students have null for start_time, end_time).

Here is my current sql:

SELECT c.id classId, sc.id mapperId, a.start_time startTime, a.end_time endTime FROM class c
JOIN student_class sc ON sc.class_id = c.id
LEFT JOIN attendance a ON a.student_class_id = sc.id;

The result should look like this.

classId mapperId startTime endTime
1 1 09:00:00 10:00:00
1 2 NULL NULL
1 3 NULL NULL

Upvotes: 3

Views: 486

Answers (3)

Gharbad The Weak
Gharbad The Weak

Reputation: 1641

@user you can get the result set you are looking for my using another instance of your query modified slightly as a sub query, like this:

SELECT c.id classId, 
    sc.id mapperId, 
    a.start_time startTime, 
    a.end_time endTime 
FROM class c
JOIN student_class sc ON sc.class_id = c.id
LEFT JOIN attendance a ON a.student_class_id = sc.id
WHERE `c`.`id` IN (  
  SELECT DISTINCT c.id classId
  FROM class c
  JOIN student_class sc ON sc.class_id = c.id
  JOIN attendance a ON a.student_class_id = sc.id
  )

Here is a mock up of the answer on sqlfiddle.com

Upvotes: 1

alobe
alobe

Reputation: 101

Based on your question answer will be like this

  SELECT cls.id, stdclass.id AS mapperId , att.start_time ,att.end_time FROM attendance att INNER JOIN student_class stdclass ON att.student_class_id = stdclass.id INNER JOIN class cls ON stdclass.class_id = cls.id INNER JOIN student std ON stdclass.student_id = std.id

Upvotes: 0

Aditya mettu
Aditya mettu

Reputation: 21

Just add a condition for the class id

SELECT c.id classId, sc.id mapperId, a.start_time startTime, 
a.end_time endTime FROM class c
JOIN student_class sc ON sc.class_id = c.id
LEFT JOIN attendance a ON a.student_class_id = sc.id
where c.id in (select distinct sc.class_id 
            from attendance a 
            join student_class sc 
            on a.student_class_id = sc.id);

Upvotes: 1

Related Questions