CODEWITHSUNDEEP
mysqlsql
Wilz5363
Wilz5363

Reputation: 79

Complex query consisting 3 tables using mysql

I got 3 tables:


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;

Upvotes: 0

Views: 46

Answers (2)

Aron
Aron

Reputation: 775

Quick stab:

 SELECT st.student_id
    ,st.student_name
    ,si.InternshipCount
FROM student st
LEFT JOIN (
    SELECT count(*) AS InternshipCount
        ,student_id
    FROM student_internship s
    INNER JOIN internship i
        ON s.student_id = i.student_id
        group by student_id
    ) AS si
    ON st.student_id = si.student_id

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 2

Related Questions