Creating a SQL statement with a max in the select statement and a max in the sub-query
Posted on 2007-12-03
I am attempting to return rows from several tables. For example:
Student_ID Student_Name Degree_ID Student_ID Degree_Major Degree_Qual
1 Michael Jackson 1 1 Fianance Bachelor
2 Janet Jackson 2 1 Economics Bachelor
3 Tito Jackson 3 2 Math Master
4 3 English Doctorate
Institute_ID Student_ID Institute_Name Institute_GradDate
1 1 GSU 05/15/2007
2 3 FSU 07/22/2007
3 2 OSU 03/31/2006
4 1 TSU 12/15/2007
5 2 OU 07/15/2006
As you can see, Student is the master table and is a foreigh column in the other tables. If a student is listed more than once, then it implies that the student switched majors or transferred to a new school.
I need the Student_Name, Degree_Mafor, Degree_Qual, and Institute_Name for each student who graduated/will graduae this year.
Michael Jackson Economics Bachelor TSU
Tito Jackson English Doctorate FSU
I am trying to avoid several sub-queries for the max id for each student-table combo. Right now I have:
WHERE (YEAR(Institute.Institute_GradDate) = YEAR(GETDATE()))
Each sub-query represents the max id for waech student for each table (I have six tables that I am getting the data from hence there will be five sub-queries).
Any suggestions? If this doesn't make sense, let me know.