SQL how to query on self-join

Vote count:

0

A sql question confused me a lot, I have two tables like this

create table faculty
(fac No
 fac first name
 fac last name
 fac supervisor No)

another is

create table offering
(offer No
 course No
 offterm
 offyear
 offlocation
 offime
 facNo   )

now I need to write a query that list the names of faculty members and the course number for which the faculty member teaches the same course as his or her supervisor in 2010.

I try to write a query like this

select fa.FacNo,fa.FacFirstName,fa.FacLastName
from faculty as fa,faculty as fs,Offering as o, Offering as os
where fa.FacSupervisor = fs.FacNo
and fa.FacNo = o.FacNo
and fa.FacSupervisor = os.FacNo
and o.OffYear = 2010

but I cant have the right answer, so please give me some hints

asked 30 secs ago

xiaopassion

17