Tyson Moyes Tyson Moyes - 1 year ago 67
SQL Question

Comparing Teachers Based on How Many Similar Courses They Teach

I have this code:

SELECT DISTINCT instructors.name AS name,
substring(sections.course,1,4) AS courseCode
FROM instructors
JOIN teaches ON instructors.id = teaches.instructor_id
JOIN schedules ON teaches.schedule_id = schedules.id
JOIN sections ON schedules.section_id = sections.id

This is the output of said code

What I need to do, is compare two names and see how many courseCodes they have in common. (Example: asifa amir and stephen cheung both teach a BUSI course, therefore, we add one to a "coteaches" count.

The output needs to be:

teacher1Name, teacher2Name, numberOfCoteaches

But I can't for the life of me figure out what to do to make that output.

Any ideas?

Answer Source

This will give every pair of instructors and the number of coursecodes they share.

SELECT a.name name1, b.name name2, 
    count(distinct substring(seb.course,1,4)) coteaches
FROM (instructors a 
       Join teaches ta on ta.instructor_id = a.instructor_id
       Join schedules sha on sha.id = ta.ScheduleId
       Join sections sea.id = sha.sectionId)
   join (instructors b
       Join teaches tb on tb.instructor_id = b.instructor_id
       Join schedules shb on shb.id = tb.ScheduleId
       Join sections seb on seb.id = shb.sectionId)
      on substring(seb.course,1,4) = substring(sea.course,1,4)
Group By a.name, b.name 

To do it for only one specific pair of instructors, add a where clause that restricts the output

Where a.Name = [Put one name here]
  and b.name = [Put the other name here]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download