0

I am trying to get instructors who are teaching students that live in same zipcode as the instructor.

First I found what zipcodes were shared by students and instructors using intersect.

SELECT DISTINCT FIRST_NAME,LAST_NAME,PHONE
FROM INSTRUCTOR 
 WHERE ZIP IN (
               SELECT ZIP 
               FROM STUDENT
               INTERSECT
               SELECT ZIP
               FROM INSTRUCTOR

                );

than how can I find the section_id's for all students that where in the query above ?

SELECT SECTION_ID   
FROM STUDENT s
JOIN ENROLLMENT e ON s.STUDENT_ID = e.STUDENT_ID
JOIN SECTION w ON e.SECTION_ID = w.SECTION_ID
JOIN INSTRUCTOR z ON w.INSTRUCTOR_ID = z.INSTRUCTOR_ID;

I dont know how to get inner query that would be something like INSTRUCTOR_ID.SECTION_ID = STUDENT_ID SECTION_ID,

Right now I am having in my query just returning all of the teachers who lived in that zipcode, but I also need to check for students that are taking a section that the instructors in that zip code are teaching....

SELECT DISTINCT FIRST_NAME,LAST_NAME,PHONE
FROM
    (
SELECT FIRST_NAME,LAST_NAME,PHONE,ZIP
FROM STUDENT 

    ) 
Derived
 WHERE ZIP IN (
               SELECT ZIP 
               FROM STUDENT
               INTERSECT
               SELECT ZIP
               FROM INSTRUCTOR

                );
Manual
  • 1,627
  • 5
  • 17
  • 20

2 Answers2

1
SELECT DISTINCT z.FIRST_NAME,z.LAST_NAME,z.PHONE
FROM STUDENT s
JOIN ENROLLMENT e ON s.STUDENT_ID = e.STUDENT_ID
JOIN SECTION w ON e.SECTION_ID = w.SECTION_ID
JOIN INSTRUCTOR z ON w.INSTRUCTOR_ID = z.INSTRUCTOR_ID AND s.ZIP = z.ZIP
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • I didnt know how to put them together thinking wise, I shouldve just put AND s.ZIP = z.ZIP which I did not think...thank you. – Manual Nov 02 '13 at 19:44
1

Write a query that returns instructors and their students. Add the instructor and student zip codes. Add a predicate such that the zip codes must be the same.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96