Get Common Column value for multiple rows

December 3, 2021

SELECT f1.FacNo, o1.CourseNo, f1.FacSupervisor 
from Faculty f1 
INNER JOIN Offering o1 on o1.FacNo = f1.FacNo AND o1.OffYear = 2017

is it possible to write a query which returns rows whose FacNo and FacSupervisor CourseNo are same?

I was hoping to do this without any loops or sorts.

>Solution :

It’s not entirely clear to me what you’re asking, but this may get you close:

--original query
SELECT f1.FacNo, o1.CourseNo, f1.FacSupervisor 
FROM Faculty f1 
INNER JOIN Offering o1 on o1.FacNo = f1.FacNo AND o1.OffYear = 2017
 -- further narrow results for offerings with a supervisor matching the faculty member
INNER JOIN Offering o2 ON o2.FacNo = f1.FacSupervisor 
    -- for the same year and course
    AND o2.OffYear = o1.OffYear AND o2.CoursNo = o1.CourseNo