MySql SELECT with Joins and conditions statement

Question

I have three tables

TABLE `courses` (
  id                int         NOT NULL UNIQUE AUTO_INCREMENT,
  title             varchar(50) NOT NULL UNIQUE,
  duration          int         NOT NULL,
  theme             varchar(50) NOT NULL,
  students_quantity int         NOT NULL,
  PRIMARY KEY (id)
);
TABLE `users` (
  id       int         NOT NULL UNIQUE AUTO_INCREMENT,
  name     varchar(50) NOT NULL,
  email    varchar(50) NOT NULL UNIQUE,
  password varchar(50) NOT NULL,
  status   varchar(20) NOT NULL,
  role_id  int         NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (role_id) REFERENCES `roles` (id)
);
TABLE `teachers_courses` (
  teacher_id int NOT NULL,
  course_id  int NOT NULL,
  PRIMARY KEY (teacher_id, course_id),
  FOREIGN KEY (teacher_id) REFERENCES `users` (id),
  FOREIGN KEY (course_id) REFERENCES `courses` (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

How can I get get courses.* and users.name AS teacher for this course, and if I have not course_id and teacher_id for this course in teachers_courses I'll get 'none' in teacher?

Answer 1

Use JOIN to combine your data following primary key - foreign key path. Function coalesce() would return the second argument, if the first one evaluates to NULL.

select c.*, coalesce(u.name, 'none') as teacher
from courses c
left join teachers_courses tc on c.id = tc.course_id
left join users u on tc.teacher_id = u.id
order by c.id

Since there can be multiple teachers for each course, the only case in which you would get 'none' as teacher value would be if there is no teacher assigned for a course (not even one). If there is more than one teacher, there will be as many rows in the output as there are teachers for each course, thus I included ORDER BY to sort the result properly.

If you need to view the data for only one course, include a WHERE condition like this:

-- ... Above SQL here ...
WHERE c.id = ?