Wilz5363 Wilz5363 - 1 year ago 41
SQL Question

Complex query consisting 3 tables using mysql

I got 3 tables:

  • student

  • internship

  • student_internship

create table student(student_id varchar(45), student_name varchar(45));
create table internship(internship_id varchar(45), internship_name varchar(45));

and student_intership is the 'bridge' of the two tables.

student_intership(student_id, internship_id)

So, it is a many to many situation.


I want to get the name of the internship and number of student, but
if there's no student for that internship, so it should have the following example:

intership_name | count(student_id)
1. intern1 | 20
2. intern2 | 3
3. intern3 | 0

the code i have tried:

select internship.internship_id, count(student.student_id)
from student_internship, internship, student
where student_internship.student_id = student.student_id
and student_internship.internship_id = internship.internship_id
group by student_internship.internship_id;

Answer Source

Try this:

SELECT i.internship_name, COALESCE(COUNT(si.student_id), 0) AS cnt
FROM internship i
LEFT JOIN student_intership si ON i.internship_id = si.internship_id
GROUP BY si.internship_id, i.internship_name

The above query will return all records of table internship. It will return 0 for internship records having no relation to student records.