加入多个表 [英] 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

SQL 小提琴

推荐答案

我相信这就是您要找的:

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屋！