first time asking a question on Stack Overflow... Amazing resource, but there's just one thing that's really baffling me as a newcomer to SQL.
第一次在Stack Overflow上问一个问题......令人惊讶的资源,但只有一件事让我感到困惑的是作为SQL的新手。
I have three tables and I would like to obtain the names of all the Mentors who are linked to Bob's students.
我有三张桌子,我想获得与Bob的学生有联系的所有导师的名字。
Table 1: TEACHERS
表1:教师
================
ID Name
================
1 Bob
Table 2: STUDENTS
表2:学生
===================================
STUDENT_ID Name TEACHER_ID
===================================
1 Jayne 1
2 Billy 5
3 Mark 2
Table 3: MENTOR_RELATIONSHIPS
表3:MENTOR_RELATIONSHIPS
==============================
ID STUDENT_ID MENTOR_ID
==============================
1 1 3
2 2 2
3 3 3
Table 4: MENTORS
表4:导师
=====================
MENTOR_ID Name
=====================
1 Sally
2 Gillian
3 Sean
I would like to run a query to find all of the mentors of Bob's students. So the mentors for all students with TEACHER_ID = 1
我想运行一个查询来查找Bob的学生的所有导师。因此,所有学生的导师都是TEACHER_ID = 1
In this case Sean would be the result.
在这种情况下,肖恩就是结果。
I know that it is something to do with Joins, or could I find this using a normal query??
我知道它与Joins有关,或者我可以使用普通查询找到它吗?
Any help is much appreciated! Many thanks...
任何帮助深表感谢!非常感谢...
4 个解决方案
#1
5
this should do the work
这应该做的工作
select distinct m.name from students s
inner join mentor_ralationships mr on mr.student_id=s.student_id
inner join mentors m on m.mentoir_id=mr.mentor_id
where s.teacher_id=1;
#2
0
Without joins (not preferred)
没有连接(不是首选)
SELECT mentors.name FROM mentors
WHERE mentors.id
IN (SELECT MENTOR_RELATIONSHIPS.mentor FROM MENTOR_RELATIONSHIPS
WHERE MENTOR_RELATIONSHIPS.student
IN (SELECT students.id FROM students WHERE students.teacher
= (SELECT teachers.id FROM teachers WHERE teachers.name = 'bob')));
#3
-1
You could try the following:
您可以尝试以下方法:
SELECT DISTINCT m.name
FROM students s INNER JOIN TEACHERS t ON t.ID = a.TEACHER_ID
INNER JOIN MENTOR_RELATIONSHIPS mr ON mr.Student_id = s.Student_id
INNER JOIN Mentors m ON mr.MENTOR_ID = s.MENTOR_ID
WHERE s.teacher_id = 1 WHERE t.Name = 'Bob';
#4
-1
SELECT TEACHER.NAME, STUDENTS.NAME AS STUDENT NAME, MENTORS.NAME AS MENTOR
FROM TEACHERS JOIN STUDENTS ON TEACHERS.ID = STUDENTS.TEACHER_ID
JOIN MENTOR_RELATIONSHIPS ON STUDENTS.STUDENT_ID =
MENTOR_RELATIONSHIPS.STUDENT_ID
JOIN MENTORS ON MENTOR_RELATIONSHIPS.MENTOR_ID = MENTORS.MENTOR_ID
WHERE TEACHER.NAME = 'Bob' ;
#1
5
this should do the work
这应该做的工作
select distinct m.name from students s
inner join mentor_ralationships mr on mr.student_id=s.student_id
inner join mentors m on m.mentoir_id=mr.mentor_id
where s.teacher_id=1;
#2
0
Without joins (not preferred)
没有连接(不是首选)
SELECT mentors.name FROM mentors
WHERE mentors.id
IN (SELECT MENTOR_RELATIONSHIPS.mentor FROM MENTOR_RELATIONSHIPS
WHERE MENTOR_RELATIONSHIPS.student
IN (SELECT students.id FROM students WHERE students.teacher
= (SELECT teachers.id FROM teachers WHERE teachers.name = 'bob')));
#3
-1
You could try the following:
您可以尝试以下方法:
SELECT DISTINCT m.name
FROM students s INNER JOIN TEACHERS t ON t.ID = a.TEACHER_ID
INNER JOIN MENTOR_RELATIONSHIPS mr ON mr.Student_id = s.Student_id
INNER JOIN Mentors m ON mr.MENTOR_ID = s.MENTOR_ID
WHERE s.teacher_id = 1 WHERE t.Name = 'Bob';
#4
-1
SELECT TEACHER.NAME, STUDENTS.NAME AS STUDENT NAME, MENTORS.NAME AS MENTOR
FROM TEACHERS JOIN STUDENTS ON TEACHERS.ID = STUDENTS.TEACHER_ID
JOIN MENTOR_RELATIONSHIPS ON STUDENTS.STUDENT_ID =
MENTOR_RELATIONSHIPS.STUDENT_ID
JOIN MENTORS ON MENTOR_RELATIONSHIPS.MENTOR_ID = MENTORS.MENTOR_ID
WHERE TEACHER.NAME = 'Bob' ;