加入多个表 [英] Join with multiple table
问题描述
我有三个表 professor
,student
和 pro_stu
其中 pro_stu
包含 professor<之间的关系/code> 和
student
表.
I have three table professor
,student
and pro_stu
where pro_stu
contain relationship between professor
and student
table.
教授(PID、PNAME).
Professor (PID, PNAME).
学生(SID、SNAME).
Student (SID, SNAME).
PRO_STU(PSID、PID、SID).
PRO_STU (PSID, PID, SID).
我想记录所有学生向所有教授学习的所有学生.如果您需要更多信息,我会提供.我知道我必须使用 join 但我不知道如何使用它.谢谢.
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.
我想要学生 id=11,名字是 hasan,所有教授都学过.
我尝试了以下查询,但没有成功
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
推荐答案
我相信这就是您要找的:
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)
这篇关于加入多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!