mysql - Join with multiple table

Question

I have three table professor,student and pro_stu where pro_stu contain relationship between professor and student table.

Professor (PID, PNAME).

Student (SID, SNAME).

PRO_STU (PSID, PID, SID).

I want to all student records which student learn from all professor. If you required further information I will give it. I know i have to use join but how to use it I don't know. Thanks.

I want student id=11 which name is hasan which learn by all professor.

I tried following query but no luck

SELECT *
FROM student
INNER JOIN pro_stu ON pro_stu.sid=student.sid
INNER JOIN professor ON professor.pid = pro_stu.pid

SQL Fiddle

Answer 1

I believe here is what you looking for:

SELECT SID, SNAME, count(DISTINCT PID) AS `c` FROM PRO_STU 
INNER JOIN Student USING (SID)
GROUP BY SID, SNAME
HAVING `c` = (SELECT count(*) FROM Professor)