MySQL: List instructors (names) who taught a section of CS160 and a section of CS340 in 99F term.

Question

The schema is as follows:

Student(Snum, Sname)
Course(Cnum, Cname)
Professor(Pnum,Pname, Dept, Office)
Class(Cnum, Term, Section, Instructor)

How can I join the two selects below to get Instructors who taught both CS160 and CS340?

SELECT DISTINCT Instructor FROM class
WHERE Term = "99F" AND Cnum = "CS160"
SELECT DISTINCT Instructor FROM class
WHERE Term = "99F" AND Cnum = "CS340"

Thanks!

Answer 1

Since MySql doesn't have intersect, you have to do a self-join; something like:

SELECT DISTINCT a.Instructor FROM class a inner join class b
using (Instructor,Term)
where a.Term "99F" and a.Cnum = "CS160" and b.Cnum = "CS340"

---

Edit: with intersect, you just put the intersect specifier between the 2 queries you had in your example (and you can omit the "distinct"; "intersect" returns only distinct values):

SELECT Instructor FROM class
WHERE Term = "99F" AND Cnum = "CS160"
INTERSECT
SELECT Instructor FROM class
WHERE Term = "99F" AND Cnum = "CS340"

intersect is part of the SQL standard, but MySql doesn't implement it. SQL implementations that do have intersect include Oracle and Postgres.

See also mySQL versus Standard SQL