How would you solve this tricky SQL problem?

Question

Say you have a table layout like the following:

couses:

id (INT), 
courseName (VARCHAR)

instructors:

id (INT),
courseId(INT),
instructor(VARCHAR)

Create a query which will print the all the courses and if one instructor is present display their name, if two instructors are present print both of their names on the row in sorted order, if more than two instructors are present instead of the instructors names display “committee“.

For instance your output would look something like this

    courseId    instructor1     instructor2 
    0           Edward Yourdon  
    1           Edward Dijkstra    Nicholas Wirth 
    2           Comittee  

Note: Taken from a questionnaire on TheDailyWtf. Not a homework question.

Answer 1

Yes, yes, business logic, etc. It's a game, not your boss asking you to do it.

In T-SQL:

 select
    id
     , courseName
     , case (select count(*) from instructors i where i.courseid=c.courseid)
      when 0 then 'No Instructor'
      when 1 then (select top 1 instructor from instructors where i.courseid=c.courseid)
      when 2 then (select top 1 instructor from instructors where i.courseid=c.courseid order by instructor desc)
      else 'Committee'
      end as instructor_1
     , case (select count(*) from instructors i where i.courseid=c.courseid)
      when 2 then (select top 1 instructor from instructors where i.courseid=c.courseid order by instructor asc)
      else ''
      end as instructor_2
 from courses c

Answer 2

What you are looking for is a "Pivot table" or "Cross-tab" report. They are available using normal SQL:

http://en.wikibooks.org/wiki/MySQL/Pivot_table

There are likely other questions here on SO with more information as well. Check out the information here, which shows how you might do it in SQL, or in the application logic (through successive queries):

http://stackoverflow.com/search?q=pivot+table

Answer 3

In SQL Anywhere, here's how you could do it:

select courseid as cid,
if ( select count(*) from instructor where courseid = cid ) > 2 
   then 'Committee' 
else 
   list(name order by name) 
endif as profs
from instructor
group by courseid
order by cid

Note that this selects 'profs' as the list of professors (as stated in the question) as a single column.

I'm not familiar enough with MySQL to know if there is an equivalent to the list() function.

Answer 4

Not the best query in the world, but in all it's ugliness, I kind of like it. Specifically, I like the fact that you don't have to deal with multiple case statements, which can be a pain if you have many fields on which you have to apply the case to:

--- Single instructor case.
select
    c.id as courseId, i.instructor as instructor1, null as instructor2
from
    courses as c inner join instructors as i on i.courseId = c.id
where
    (
        select 
            count(instructor) 
        from 
            instructors as i2 
        where 
            i2.courseId = c.id
    ) = 1
union
--- Committee case.
select
    c.id as courseId, "committee" as instructor1, null as instructor2
from
    courses as c inner join instructors as i on i.courseId = c.id
where
    (
        select 
            count(instructor) 
        from 
            instructors as i2 
        where 
            i2.courseId = c.id
    ) > 2
union
--- Two instructor case.
select
    c.id as courseId, i1.instructor as instructor1, 
    i2.instructor as instructor2
from
    courses as c, instructor as i1, instructor as i2
where
    --- Course ids must match.
    c.id = i1.courseId and c.id = i2.courseId and

    --- Instructor ids do not match.
    i1.id <> i2.id and

    --- There are only two instructors.
    (
        select 
            count(instructor) 
        from 
            instructors as i2 
        where 
            i2.courseId = c.id
    ) > 2

Answer 5

I think this will work, but I have not tested it yet. It is not very scalable if you decide that you want to start showing 3 instructors or 4 or more though.

SELECT
    C.id AS course_id,
    CASE
     WHEN I3 IS NOT NULL THEN 'Committee'
     ELSE I1.instructor + COALESCE(', ' + I2.instructor, '')
    END AS instructors
FROM
    Courses C
LEFT OUTER JOIN Instructors I1 ON
    I1.course_id = C.id
LEFT OUTER JOIN Instructors I_CHK1 ON
    I_CHK1.course_id = C.id AND
    I_CHK1.instructor < I1.instructor
LEFT OUTER JOIN Instructors I2 ON
    I2.course_id = C.id AND
    I2.instructor > I1.instructor
LEFT OUTER JOIN Instructors I_CHK2 ON
    I_CHK2.course_id = C.id AND
    I_CHK2.instructor > I1.instructor AND
    I_CHK2.instructor < I2.instructor
LEFT OUTER JOIN Instructors I_CHK2 ON
    I3.course_id = C.id AND
    I3.instructor > I2.instructor AND
WHERE
    I_CHK1.id IS NULL AND
    I_CHK2.id IS NULL

Answer 6

Yet another alternative. Note:

1- I do not believe any solutions presented so far are portable between SQL dialects. If a 4th column count(*) was allowed I believe the following solution could be quite portable as it mostly uses "standard SQL". However I only tested on SQLite3.

2- I purposely stick to your specification by reproducing the typo for the couses table. [ I guess it was intended to be courses instead of couses ]

select 
  c.id, c.courseName, i.instructor as instructor1, null as instructor2 
from 
  couses c, instructors i 
where 
  c.id = i.courseId 
group by 
  courseId having count(*) = 1
union
select /* Case 2: Two instructors */
  c.id, c.courseName, i1.instructor as instructor1, i2.instructor as instructor2 
from 
  couses c, instructors i1, instructors i2 
where 
  c.id = i1.courseId and c.id = i2.courseId and i1.id != i2.id and i1.id < i2.id 
group by 
  c.id having count(*) = 1
union
select /* Case 3: Three or more instructors */
  c.id, c.courseName, "Commitee" as instructor1, null as instructor2 
from 
  couses c, instructors i1, instructors i2, instructors i3 
where 
  c.id = i1.courseId and c.id = i2.courseId and c.id = i3.courseId and 
  i1.id != i2.id and i1.id != i3.id and i2.id != i3.id and i1.id < i2.id 
  and i2.id < i3.id