CODEWITHSUNDEEP
sqljoininner-join
user2751653
user2751653

Reputation: 57

SQL Query (or Join) for 3 tables

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.

Table 1: TEACHERS

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

Table 2: STUDENTS

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

Table 3: MENTOR_RELATIONSHIPS

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

Table 4: MENTORS

=====================
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

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??

Any help is much appreciated! Many thanks...

Upvotes: 3

Views: 39244

Answers (5)

Ishwor Khanal
Ishwor Khanal

Reputation: 1390

It could be helpful for you as I had to retrieve data from three tables AssignedSubject, Section and SchoolClass when a teacher assigned to specific subject then I have to find out the his class and section details including subjectid which I did this way

select a.StaffID, a.SubjectID, s.ID as SectionId, s.Name as SectionName, S.Remarks as SectionRemarks, sc.ID as ClassId, sc.Name as ClassName, sc.Remarks as ClassRemarks from AssignedSubject a
     inner join Section s on a.SectionId=s.ID 
     inner join SchoolClass sc on sc.ID=s.ClassId  where a.StaffID=3068

Upvotes: 0

Subhashc
Subhashc

Reputation: 63

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')));

Upvotes: 0

Harish Padmalochanan
Harish Padmalochanan

Reputation: 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' ;

Upvotes: -1

Saurabh Mishra
Saurabh Mishra

Reputation: 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';

Upvotes: -1

danisius
danisius

Reputation: 597

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;

Upvotes: 9

Related Questions