Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm writing a query to list the first and last names of all staff who have ever taught any of the same course as Kim (Staff_Fname) Cox (Staff_Lname) or have ever taught in the same room (Loc_id) as Kim Cox.

So far I have written this (and it's full of errors):

SELECT KimClasses.Course_Code, KimClasses.Course_Name, St2.Staff_Fname, St2.Staff_Lname
FROM
(SELECT St.Staff_id, CS.C_SE_id, C.Course_code, C.Course_name
FROM Staff St
JOIN CourseSection CS ON St.Staff_id = CS.Staff_ID
JOIN Location L ON CS.Loc_ID = L.Loc_id
JOIN Course C ON CS.Course_ID = C.Course_ID
WHERE St.Staff_Fname = "Kim"
    AND St.Staff_Lname = "Cox") KimClasses
JOIN CourseSection CS2 ON KimClasses.Course_ID = CS2.Course_ID
    AND NOT KimClasses.Staff_id = St2.Staff_id;

But I'm not sure on how I would include the part to list the staff that have taught in the same room.

Database schema: http://i.stack.imgur.com/dTGV5.jpg

share|improve this question
Post sample data and desired output in a tabular form or better yet as a sqlfiddle – peterm 8 hours ago

1 Answer

up vote 1 down vote

This should get you started...

 Select s.staff_fname,s.staff_lname
 from staff s join course_section cs
 on s.staff_if = cs.staff_id
 where cs.course_id in (select distinct s1.course_id from staff s1 join course_section sc1 where s1.Staff_Fname = 'kim' and s1.Staff_Lname = "Cox" )
 OR s.loc_id in  (select distinct s2.loc_id from staff s2 join course_section sc2 where s2.Staff_Fname = 'kim' and s2.Staff_Lname = "Cox" )
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.