Select all Courses, and for those in which a certain Student is enrolled, also select the Enrollment data

user_952808 0
Viewed 443

Classic Student-Enrollment-Course structure, but the Enrollment join table has an additional DATE column. Here's a sample fiddle: http://sqlfiddle.com/#!9/81d3e5/3.

CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE course (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE enrollment (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrollment_date DATE NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (course_id) REFERENCES course(id)  
);

INSERT INTO student (name) VALUES
("Alice"),
("Bob");

INSERT INTO course (name) VALUES
("CS 101"),
("Relational Algebra"),
("Compilers");

INSERT INTO enrollment (student_id, course_id, enrollment_date) VALUES
(1, 1, '2020-01-13'),
(1, 2, '2020-02-05'),
(2, 2, '2020-02-07');

A student needs to see a list of all available courses, and if they're enrolled, the enrollment date. Here's the desired result for student ID 2:

+--------------------+-----------------+
|    course_name     | enrollment_date |
+--------------------+-----------------+
| CS 101             | null            |
| Relational Algebra | 2020-02-07      |
| Compilers          | null            |
+--------------------+-----------------+

It's been a few years since I did anything more complicated than a simple left join, and I can't figure this one out.

mysql sql
3 Answers

You can create a INNER table and left join the result of the table with the course table.
Note: student table is optional I just used so that you can retrieve student information as well

SELECT
c.name course,
es.enrollment_date
FROM course c
LEFT JOIN 
(SELECT course_id , e.`enrollment_date` from enrollment e , student s where e.student_id = s.id and s.id=3) 
AS es on c.id = es.course_id;
user_136120960 
SELECT course.name, t1.enrollment_date 
FROM course 
LEFT JOIN (SELECT * FROM enrollment WHERE enrollment.student_id = 2) AS t1 
ON course.id = t1.course_id;
user_120971340

Use below query, left outer join will provide you your expected result

select c.name as course_name, e.enrollment_date from course c 
left outer join enrollment e
on (c.id = e.student_id);


select c.name as course_name, e.enrollment_date from course c 
left outer join enrollment e
on (c.id = e.student_id)
where e.student_id = 2;
user_44736150
Related