This is a toy example where we have 3 tables:
- Student(snum, sname)
- Enrolled(snum, cname)
- Class(cname, meets_at)
and we want to find the sname of the students that are enrolled in at least two classes that meet at the same time.
Here are three attempts, which one is best and why ?
select sname, (count(distinct cname) - count(distinct meets_at)) from (select st.*, cl.* from Student st, Class cl, Enrolled en where st.snum = en.snum and cl.cname = en.cname) group by sname having count(distinct cname) - count(distinct meets_at) >= 1;
select S.sname from Student S where S.snum in ( select E.snum from Enrolled E, Class C where E.cname = C.cname group by E.snum, C.meets_at having count(E.cname) >= 2 );
select S.sname from Student S where S.snum in ( select E1.snum from Enrolled E1, Enrolled E2, Class C1, Class C2 where E1.snum = E2.snum and E1.cname <> E2.cname and E1.cname = C1.cname and E2.cname = C2.cname and C1.meets_at = C2.meets_at);