title: Choosing applicants for positions via a MySQL query
body: <p>I have the following MySQL tables. They represent CS courses at a school and applicants to be a TA (teaching assistant) for specific courses.</p>
  
  <p>I want to create a query that will print the "best" applicant for each course. The constraints for the best applicant are:</p>
  
  <ol>
  <li>Applicants with <code>Applicants.level</code> = 7 are matched first.</li>
  <li>Applicants with <code>ApplicantsToCourses.returning</code> = true are chosen second.</li>
  <li>All other applicants are matched without further discrimination.</li>
  </ol>
  
  <p>The table definitions are:</p>
  
  <pre><code>CREATE TABLE Courses (
   course_number SMALLINT(3) UNSIGNED NOT NULL,
   course_section SMALLINT(1) UNSIGNED NOT NULL,
   name CHAR(30) NOT NULL,
   instructor CHAR(30),
   lab_time CHAR(30),
   PRIMARY KEY(course_number, section),
   FOREIGN KEY(course_number, section) REFERENCES ApplicantsToCourses(course_number, course_section)
  )
  
  CREATE TABLE Applicants (
   student_id CHAR(10) NOT NULL,
   name CHAR(30),
   email CHAR(30),
   gpa DECIMAL(4,3) UNSIGNED,
   level CHAR(2),
   timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY(student_id),
   FOREIGN KEY(student_id) REFERENCES ApplicantsToCourses(student_id),
   CHECK(gpa &lt;= 4.000)
  )
  
  CREATE TABLE ApplicantsToCourses (
   student_id CHAR(10) NOT NULL,
   returning BOOLEAN DEFAULT FALSE NOT NULL,
   course_number SMALLINT(3) UNSIGNED NOT NULL,
   course_section SMALLINT(1) UNSIGNED NOT NULL,
   PRIMARY KEY(student_id, course_number, course_section),
   FOREIGN KEY(student_id) REFERENCES Applicants(student_id),
   FOREIGN KEY(course_number, course_section) REFERENCES Courses(course_number, course_section)
  )
  </code></pre>
  
  <p>My attempt at a query was . . .</p>
  
  <pre><code>select a.student_id, ac.course_number, ac.course_section
  from Applicants a, ApplicantsToCourses ac, Courses c
  where a.student_id = ac.student_id and ac.course_number = c.course_number and ac.course_section = c.course_section
  order by a.level, ac.returning desc
  </code></pre>
  
  <p>. . . but that certainly doesn't have the correct logic.</p>