title: MySQL LEFT OUTER JOIN virtual table
body: <p>I am working on a pretty complicated query let me try to explain it to you. Here is the tables that I have in my MySQL database:</p>
  
  <p>students Table</p>
  
  <pre><code>--- `students` ---
  student_id    first_name    last_name    current_status    status_change_date
  ------------  ------------  -----------  ----------------  --------------------
  1             John          Doe          Active            NULL
  2             Jane          Doe          Retread           2012-02-01
  </code></pre>
  
  <p>students_have_courses Table</p>
  
  <pre><code>--- `students_have_courses` ---
  students_student_id    courses_course_id    s_date      e_date      int_date
  ---------------------  -------------------  ----------  ----------  -----------
  1                      1                    2012-01-01  2012-01-04  2012-01-05
  1                      2                    2012-01-05  NULL        NULL
  2                      1                    2012-01-10  2012-01-11  NULL
  </code></pre>
  
  <p>students_have_optional_courses Table</p>
  
  <pre><code>--- `students_have_optional_courses` ---
  students_student_id    optional_courses_opcourse_id    s_date      e_date
  ---------------------  ------------------------------  ----------  ----------
  1                      1                               2012-01-02  2012-01-03
  1                      1                               2012-01-06  NULL
  1                      5                               2012-01-07  NULL
  </code></pre>
  
  <p>Here is my query so far</p>
  
  <pre><code>SELECT 
    `students_and_courses`.student_id,
    `students_and_courses`.first_name,
    `students_and_courses`.last_name,
    `students_and_courses`.courses_course_id,
    `students_and_courses`.s_date,
    `students_and_courses`.e_date,
    `students_and_courses`.int_date,
    `students_have_optional_courses`.optional_courses_opcourse_id,
    `students_have_optional_courses`.s_date,
    `students_have_optional_courses`.e_date
  FROM (
    SELECT
      `c_s_a_s`.student_id,
      `c_s_a_s`.first_name,
      `c_s_a_s`.last_name,
      `c_s_a_s`.courses_course_id,
      `c_s_a_s`.s_date,
      `c_s_a_s`.e_date,
      `c_s_a_s`.int_date
   FROM (
     SELECT
        `students`.student_id,
        `students`.first_name,
        `students`.last_name,
        `students_have_courses`.courses_course_id,
        `students_have_courses`.s_date,
        `students_have_courses`.e_date,
        `students_have_courses`.int_date
      FROM
        `students`
        LEFT OUTER JOIN `students_have_courses`
          ON (
            `students_have_courses`.`students_student_id` = `students`.`student_id` AND     (( 
            `students_have_courses`.`s_date` &gt;= `students`.`status_change_date` AND
            `students`.current_status = 'Retread' ) OR
            `students`.current_status = 'Active')
          )
        WHERE
          `students`.current_status = 'Active' OR
          `students`.current_status = 'Retread'
    ) `c_s_a_s`
    ORDER BY
      `c_s_a_s`.`courses_course_id` DESC
  ) `students_and_courses`
  LEFT OUTER JOIN `students_have_optional_courses`
    ON (
      `students_have_optional_courses`.students_student_id =     `students_and_courses`.student_id AND
      `students_have_optional_courses`.s_date &gt;= `students_and_courses`.s_date AND 
      `students_have_optional_courses`.e_date IS NULL
    )
  GROUP BY
   `students_and_courses`.student_id;
  </code></pre>
  
  <p>What I want to be returned is the student_id, first_name, and last_name for all Active or Retread students and then LEFT JOIN the highest course_id, s_date, e_date, and int_date for the those students where the s_date is since the status_change_date if status is 'Retread'. Then LEFT JOIN the highest optional_courses_opcourse_id, s_date, and e_date from the students_have_optional_courses TABLE where the students_have_optional_courses.s_date is greater or equal to the students_have_courses.s_date and the students_have_optional_courses.e_date IS NULL</p>
  
  <p>Here is what is being returned:</p>
  
  <pre><code>student_id    first_name    last_name    courses_course_id    s_date      e_date      int_date      optional_courses_opcourse_id    s_date_1    e_date_1
  ------------  ------------  -----------  -------------------  ----------  ----------  ------------  ------------------------------  ----------  ----------
  1             John          Doe          2                    2012-01-05  NULL        NULL          1                               2012-01-06  NULL
  2             Jane          Doe          NULL                 NULL        NULL        NULL          NULL                            NULL        NULL
  </code></pre>
  
  <p>Here is what I want being returned:</p>
  
  <pre><code>student_id    first_name    last_name    courses_course_id    s_date      e_date      int_date      optional_courses_opcourse_id    s_date_1    e_date_1
  ------------  ------------  -----------  -------------------  ----------  ----------  ------------  ------------------------------  ----------  ----------
  1             John          Doe          2                    2012-01-05  NULL        NULL          5                               2012-01-07  NULL
  2             Jane          Doe          NULL                 NULL        NULL        NULL          NULL                            NULL        NULL
  </code></pre>
  
  <p>Everything is working except one thing, I cannot seem to get the highest students_have_optional_courses.optional_courses_opcourse_id no matter how I form the query </p>
  
  <p>Sorry, I just solved this myself after writing this all out I think it helped me think of the solution. </p>
  
  <p>Here is the solution query:</p>
  
  <pre><code>    SELECT 
        `students_and_courses`.student_id,
        `students_and_courses`.first_name,
        `students_and_courses`.last_name,
        `students_and_courses`.courses_course_id,
        `students_and_courses`.s_date,
        `students_and_courses`.e_date,
        `students_and_courses`.int_date,
        `students_optional_courses`.optional_courses_opcourse_id,
        `students_optional_courses`.s_date,
        `students_optional_courses`.e_date
      FROM (
        SELECT
          `c_s_a_s`.student_id,
          `c_s_a_s`.first_name,
          `c_s_a_s`.last_name,
          `c_s_a_s`.courses_course_id,
          `c_s_a_s`.s_date,
          `c_s_a_s`.e_date,
          `c_s_a_s`.int_date
        FROM (
          SELECT
            `students`.student_id,
            `students`.first_name,
            `students`.last_name,
            `students_have_courses`.courses_course_id,
            `students_have_courses`.s_date,
            `students_have_courses`.e_date,
            `students_have_courses`.int_date
          FROM
            `students`
            LEFT OUTER JOIN `students_have_courses`
              ON (
                `students_have_courses`.`students_student_id` = `students`.`student_id` AND (( 
                `students_have_courses`.`s_date` &gt;= `students`.`status_change_date` AND
                `students`.current_status = 'Retread' ) OR
                `students`.current_status = 'Active')
              )
            WHERE
              `students`.current_status = 'Active' OR
              `students`.current_status = 'Retread'
        ) `c_s_a_s`
        ORDER BY
          `c_s_a_s`.`courses_course_id` DESC
      ) `students_and_courses`
      LEFT OUTER JOIN (
        SELECT
          *
        FROM
          `students_have_optional_courses`
        ORDER BY
          `students_have_optional_courses`.optional_courses_opcourse_id DESC
      ) `students_optional_courses`
        ON (
          `students_optional_courses`.students_student_id = `students_and_courses`.student_id AND
          `students_optional_courses`.s_date &gt;= `students_and_courses`.s_date AND 
          `students_optional_courses`.e_date IS NULL
        )
      GROUP BY
       `students_and_courses`.student_id;
  </code></pre>