CODEWITHSUNDEEP
mysqlsqlsql-server-2008-r2
Danny Cullen
Danny Cullen

Reputation: 1832

Joining 2 queries into one query, as subquery

My first query pulls out student details from 3 tables. Student, StudentAddress and StudentNote.

My query must list the student details, including id, name, address, note and find the earliest date they enrolled on a course.

I have created an aggregate query to do this.

SELECT STU_Name, MIN(ENR_StartDate)
FROM Student
INNER JOIN Enrolment
ON Student.STU_Student_ID = Enrolment.ENR_Student_ID
GROUP BY STU_Name

This is my main query which pull the details of the students.

SELECT STU_Name, ADD_Addr_Line_1, ADD_Addr_Line_2, ADD_Addr_Line_3, ADD_Postcode, NTE_Note, ENR_StartDate
FROM Student
INNER JOIN StudentAddress
ON Student.STU_Student_ID = StudentAddress.ADD_Student_ID
INNER JOIN StudentNote
ON Student.STU_Student_ID = StudentNote.NTE_Student_ID
INNER JOIN Enrolment
ON Student.STU_Student_ID = Enrolment.ENR_Student_ID

I want to link these queries so the result set tells me the earliest start date per student in the row.

Desired output for a student

row0: student1id, student1name, student1address, student1note1, student1startdate

row1: student1id, student1name, student1address, student1note2, student1startdate

This will allow a student to have multiple notes, but the start start will always be calculated as the same for a student.

I have built a test schema in sql fiddle.

http://sqlfiddle.com/#!2/cecea/4

Thanks for any help.

Upvotes: 2

Views: 97

Answers (2)

Jon Armstrong - Xgc
Jon Armstrong - Xgc

Reputation: 264

Note that you have no noted for one of the students. That was corrected with an outer join.

In another case, you have 2 notes for one student. So that student has two rows in the result.

To resolve that, you need to choose a specific note (first, last, etc). You can do that using the same logic that is used to find the first enrollment record for each student.

Adjusted to handle cases where the student may have more than one row for the same enrollment date. This approach can be used to break most any of these types of ties. Note that window functions are usually a better approach, but MySQL does not support them.

SELECT STU_Name
     , ADD_Addr_Line_1
     , ADD_Addr_Line_2
     , ADD_Addr_Line_3
     , ADD_Postcode
     , NTE_Note
     , Enrolment.ENR_StartDate
     , Enrolment.ENR_ID
     , Student.STU_Student_ID
     , StudentAddress.ADD_ID
     , StudentNote.NTE_ID
  FROM Student
  JOIN StudentAddress
    ON Student.STU_Student_ID = StudentAddress.ADD_Student_ID
  LEFT JOIN StudentNote
    ON Student.STU_Student_ID = StudentNote.NTE_Student_ID
  JOIN Enrolment
    ON Student.STU_Student_ID = Enrolment.ENR_Student_ID
  LEFT JOIN Enrolment AS e2
    ON Enrolment.ENR_Student_ID = e2.ENR_Student_ID
   AND (Enrolment.ENR_StartDate, Enrolment.ENR_ID) > (e2.ENR_StartDate, e2.ENR_ID)
 WHERE e2.ENR_StartDate IS NULL
;

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

You should add a table alias to all the columns to specify where they come from.

SELECT STU_Name, ADD_Addr_Line_1, ADD_Addr_Line_2, ADD_Addr_Line_3, ADD_Postcode, NTE_Note, ENR_StartDate,
    G.EarliestStartDate
FROM Student
JOIN StudentAddress
        ON Student.STU_Student_ID = StudentAddress.ADD_Student_ID
JOIN StudentNote
        ON Student.STU_Student_ID = StudentNote.NTE_Student_ID
JOIN Enrolment
        ON Student.STU_Student_ID = Enrolment.ENR_Student_ID
JOIN (
  SELECT Student.STU_Student_ID, MIN(ENR_StartDate) EarliestStartDate
  FROM Student
  INNER JOIN Enrolment
  ON Student.STU_Student_ID = Enrolment.ENR_Student_ID
  GROUP BY STU_Name
) G on G.STU_Student_ID = Student.STU_Student_ID

Upvotes: 0

Related Questions