Wilz5363 Wilz5363 - 6 months ago 10
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.

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

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.