Combing Select Statements

[rledesma]

I want to combine the following select statements so I can have 1 export file rather than 2.

This code selects student information along with their HomeRoom teacher.

SELECT students.last_name, students.first_name, students.grade_level, teachers.last_name, students.student_number
FROM students
JOIN CC ON students.id = cc.studentid
JOIN teachers ON teachers.id = cc.teacherid
WHERE (cc.course_number >= 28001 AND cc.course_number <= 28007)
AND cc.dateleft = '24-JUN-10'
AND cc.expression = '1(A)'
AND students.schoolid=47

This code selects student information along with their PE teacher, and what period number is their PE teacher.

SELECT students.last_name, students.first_name, students.grade_level, teachers.last_name, SUBSTR(cc.expression, 1, 1)-1, students.student_number
FROM students
JOIN CC ON students.id = cc.studentid
JOIN teachers ON teachers.id = cc.teacherid
WHERE (cc.course_number >= 24501 AND cc.course_number <= 24521)
AND cc.dateleft = '24-JUN-10'
AND students.schoolid=47

I am new to SQL, the above queries may not be the best coded, but they work for the data I need. If you have a suggestion on other queries to accomplish the same thing, I am open to suggestions.

I would like to combine them, in order to have one query and one export file, with the student information along with their Homeroom Teacher name, PE teacher name, and period number all in one export.

Thank you for your help.

[urtrivedi]

SELECT students.last_name, students.first_name, students.grade_level, 
CC1.last_name HOMETEACHER, CC2.last_name PETEACHER,  CC2.PERIOD, students.student_number 

FROM students 

JOIN ( 
				SELECT cc.studentid,teachers.last_name from CC JOIN teachers ON teachers.id = cc.teacherid WHERE (cc.course_number >= 28001 AND cc.course_number <= 28007) AND cc.dateleft = '24-JUN-10'AND cc.expression = '1(A)'
)CC1 ON students.id = cc1.studentid

JOIN (
						SELECT cc.studentid,teachers.last_name, SUBSTR(cc.expression, 1, 1)-1 period FROM  CC  JOIN teachers ON teachers.id = cc.teacherid WHERE (cc.course_number >= 24501 AND cc.course_number <= 24521)AND cc.dateleft = '			24-JUN-10'
						) CC2 ON students.id = cc2.students
						
WHERE students.schoolid=47

[rledesma]

Thank you very much. It worked perfectly!

You had a minor error on line 12 from your code I fixed (CC2 ON students.id = cc2.students, should have been CC2 ON students.id = cc2.studentid), and posted the correct code here, in case anyone needs to reference this thread in the future.

Thank you again, this will save a lot of time.

SELECT students.last_name, students.first_name, students.grade_level, CC1.last_name HOMETEACHER, CC2.last_name PETEACHER, CC2.PERIOD, students.student_number
FROM students
JOIN (
      SELECT cc.studentid,teachers.last_name 
      FROM CC 
      JOIN teachers ON teachers.id = cc.teacherid 
      WHERE (cc.course_number >= 28001 AND cc.course_number <= 28007) 
      AND cc.dateleft = '24-JUN-10'
      AND cc.expression = '1(A)'
      )
      CC1 ON students.id = cc1.studentid
JOIN (
      SELECT cc.studentid, teachers.last_name, SUBSTR(cc.expression, 1, 1)-1 period 
      FROM CC 
      JOIN teachers ON teachers.id = cc.teacherid 
      WHERE (cc.course_number >= 24501 AND cc.course_number <= 24521)
      AND cc.dateleft = ' 24-JUN-10'
      )
      CC2 ON students.id = cc2.studentid
WHERE students.schoolid = 47