CODEWITHSUNDEEP
sqljoin
xiaopassion
xiaopassion

Reputation: 73

SQL how to query on self-join

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

Thanks for every one, I worked it out as below

select f.FacFirstName,F.FacLastName,s.FacFirstName as SupFirstname,s.FacFirstName as        SupLasName,o.CourseNo
from faculty as f, Faculty as s ,Offering as o, Offering as os
where f.FacSupervisor = os.FacNo
and f.FacNo = o.FacNo
and o.CourseNo = os.CourseNo
and f.FacSupervisor = s.FacNo
and o.OffYear =2010 and os.OffYear =2010

I hope this is helpful for some new SQL leaner who faces the same issue .

Upvotes: 0

Views: 185

Answers (1)

Karl Kieninger
Karl Kieninger

Reputation: 9149

"so please give me some hints"

Since that is you're question, I am happy to provide a hints.

Make your joins explicit--it helps readability immensely. For example:

FROM faculty as fa
     INNER JOIN
     faculty as fs ON fa.FacSupervisor = fs.FacNo
     INNER JOIN
     Offering as o ON fa.FacNo = o.FacNo
     INNER JOIN
     Offering as os ON fs.FacNo = os.FacNo

Once you do that I suspect you'll have much better luck seeing any problems. Or at elast if will be easier for us to help more.

You then need to filter with 

WHERE o.[course No] = os.[Course No]
  AND o.OffYear = 2010

Upvotes: 1

Related Questions