Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
396 views
in Technique[技术] by (71.8m points)

mysql - Join with multiple table

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...