CODEWITHSUNDEEP
sql
user6792790
user6792790

Reputation: 688

multi-relation queries in SQL

Given these four relations:

Mentorship (mentee_id, mentor_id)
Study (sid, credits)
Enrollment (did, sid)
Student (sid, street , city )

I want to write a query that finds all students who live in the same city and street as their mentor, assuming that all mentors and mentees are students.

I tried to write this query as:

SELECT sid
FROM Student, Mentorship
WHERE street IN (SELECT sid
                 FROM Student, Mentorship
                 WHERE mentor_sid = sid 
                   AND ...   )

It's incomplete but I tried to SELECT a sid from Student and Mentorship relations where street falls into a condition where mentor_sid and mentee_sid has same street and city.

I am new to SQL and still confused how to do these stuffs.

Upvotes: 1

Views: 1315

Answers (1)

FJT
FJT

Reputation: 2083

You need to join student (mentee) to student (mentor) via the link table

SELECT s1.* FROM Student s1
INNER JOIN Mentorship m ON s1.sid = m.mentee_id
INNER JOIN student s2 ON s2.sid = m.mentor_id 
   AND s2.street = s1.street AND s2.city = s1.city

Upvotes: 1

Related Questions