3个表的SQL查询(或联接) [英] SQL Query (or Join) for 3 tables

问题描述

第一次问有关堆栈溢出的问题...令人惊讶的资源，但是只有一件事让我作为SQL的新手感到困惑.

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.

我有三个表，我想获得与鲍勃的学生有联系的所有导师的姓名.

I have three tables and I would like to obtain the names of all the Mentors who are linked to Bob's students.

表1:老师

================
ID     Name
================
1     Bob

表2:学生

===================================
STUDENT_ID     Name     TEACHER_ID
===================================
1              Jayne    1
2              Billy    5
3              Mark     2

表3:MENTOR_RELATIONSHIPS

Table 3: MENTOR_RELATIONSHIPS

==============================
ID     STUDENT_ID    MENTOR_ID
==============================
1      1             3
2      2             2
3      3             3

表4:导师

=====================
MENTOR_ID     Name  
=====================
1            Sally
2            Gillian
3            Sean

我想运行一个查询来查找鲍勃学生的所有导师.因此，所有TEACHER_ID = 1

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

在这种情况下，肖恩就是结果.

In this case Sean would be the result.

我知道这与Joins有关，还是可以使用常规查询找到它?

I know that it is something to do with Joins, or could I find this using a normal query??

非常感谢您的帮助！非常感谢...

Any help is much appreciated! Many thanks...

推荐答案

这应该可以完成

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;

这篇关于3个表的SQL查询(或联接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！