VibertH
asked on
Creating a SQL statement with a max in the select statement and a max in the sub-query
I am attempting to return rows from several tables. For example:
Student Degree
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
Institution
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.
Result
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:
Select
(sub-query
(sub-query
(sub-query)))
WHERE (YEAR(Institute.Institute_ GradDate) = YEAR(GETDATE()))
group by
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.
Student Degree
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
Institution
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.
Result
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:
Select
(sub-query
(sub-query
(sub-query)))
WHERE (YEAR(Institute.Institute_
group by
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.
ASKER
But remember I need the max id where table1.stuId = table2.StuID. Keep in mind, in all of the table, except the master table, there can be multiple rows for each student and I need the most recen entry. Since getting the project, I have added date stamps for any additions and modifications, but the old records do not have this date stamp.
Select s.Student_Name, s.Degree_Mafor, s.Degree_Qual, i.institute_Name
FROM Student s
INNER JOIN Institution i
ON s.StudentID = i.StudentID
WHERE EXISTS (SELECT studentID, max(Year(GradDate)) FROM INSTITUTE i2 where i2.StudentID = s.StudentID group by studentid having Max(Year(GradDate)) = Year(GetDate())
--
JimFive
FROM Student s
INNER JOIN Institution i
ON s.StudentID = i.StudentID
WHERE EXISTS (SELECT studentID, max(Year(GradDate)) FROM INSTITUTE i2 where i2.StudentID = s.StudentID group by studentid having Max(Year(GradDate)) = Year(GetDate())
--
JimFive
ASKER
Take a look at my tables. Notices that the degree and institution tables have multiple records for one student. I am not concerned with the max date but the most recent transfer of a student or major change. therefore the max would be on degree_id and institute_id.
See where I am going now?
See where I am going now?
ASKER
SELECT MAX(dg.Degree_ID) AS maxStu, dg.Student_ID, dg.Degree_DegreeLevel, dg.Degree_Major
FROM Degree dg INNER JOIN
(SELECT MAX(Institute_ID) AS MaxInst, Student_ID
FROM Institute
GROUP BY Student_ID) maxINST ON dg.Student_ID = maxINST.MaxInst
WHERE (YEAR(dg.Degree_RetDate) >= YEAR(GETDATE())) AND (YEAR(dg.Degree_ExpGradDat e) >= YEAR(GETDATE()))
GROUP BY dg.Student_ID, dg.Degree_ExpDegree, dg.Degree_Program
Something more like this.
FROM Degree dg INNER JOIN
(SELECT MAX(Institute_ID) AS MaxInst, Student_ID
FROM Institute
GROUP BY Student_ID) maxINST ON dg.Student_ID = maxINST.MaxInst
WHERE (YEAR(dg.Degree_RetDate) >= YEAR(GETDATE())) AND (YEAR(dg.Degree_ExpGradDat
GROUP BY dg.Student_ID, dg.Degree_ExpDegree, dg.Degree_Program
Something more like this.
whate is degree_retdate?
ASKER
SELECT MAX(dg.Degree_ID) AS maxStu, dg.Student_ID, dg.Degree_DegreeLevel, dg.Degree_Major
FROM Degree dg INNER JOIN
(SELECT MAX(Institute_ID) AS MaxInst, Student_ID
FROM Institute
GROUP BY Student_ID) maxINST ON dg.Student_ID = maxINST.MaxInst
WHERE (YEAR(dg.Degree_ExpGradDat e) >= YEAR(GETDATE()))
GROUP BY dg.Student_ID, dg.Degree_ExpDegree, dg.Degree_Program
Here is the sample data
Student
Sudent_ID Student_FullName
111 James Bond
112 Claire Esteban
113 Tim Bob-Billy
114 Wesley Snipes
Degree
Degree_ID Student_ID Degree_Major Degree_DegreeLevel Degree_GradDate
123 111 Science Bachelor 2/2/2009
124 112 Math Doctorate 4/23/2009
125 113 English Certificate 3/12/2010
126 111 Poly-Science Bachelor 12/16/2007
127 114 Math Bachelor 8/26/2007
Institute
Institute_ID Student_ID Institute_Name
134 111 GSU
135 112 FSU
136 111 FSU
137 113 FAMU
138 114 AU
139 111 AU
These are my tables. In the degree table, sudent 111 has changed his degree once.
In the Institute table, studen 111 has changed schools twice.
FROM Degree dg INNER JOIN
(SELECT MAX(Institute_ID) AS MaxInst, Student_ID
FROM Institute
GROUP BY Student_ID) maxINST ON dg.Student_ID = maxINST.MaxInst
WHERE (YEAR(dg.Degree_ExpGradDat
GROUP BY dg.Student_ID, dg.Degree_ExpDegree, dg.Degree_Program
Here is the sample data
Student
Sudent_ID Student_FullName
111 James Bond
112 Claire Esteban
113 Tim Bob-Billy
114 Wesley Snipes
Degree
Degree_ID Student_ID Degree_Major Degree_DegreeLevel Degree_GradDate
123 111 Science Bachelor 2/2/2009
124 112 Math Doctorate 4/23/2009
125 113 English Certificate 3/12/2010
126 111 Poly-Science Bachelor 12/16/2007
127 114 Math Bachelor 8/26/2007
Institute
Institute_ID Student_ID Institute_Name
134 111 GSU
135 112 FSU
136 111 FSU
137 113 FAMU
138 114 AU
139 111 AU
These are my tables. In the degree table, sudent 111 has changed his degree once.
In the Institute table, studen 111 has changed schools twice.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again, I don't want the max grad date. just the max instituteID, degreeId, and the maxID for the other tables for the each student. (There are other tables I need info from. This is jus a subset.)
I'll try something like this and get back to you.
I'll try something like this and get back to you.
ASKER
Again, I don't want the max grad date. just the max instituteID, degreeId, and the maxID for the other tables for the each student who graduated for the current year. (There are other tables I need info from. This is jus a subset.)
I'll try something like this and get back to you.
I'll try something like this and get back to you.
ASKER
Not quite there, but getting close. It's returning multiple instances of one of the max-es. (I am using the live data and have not tested it against the sample tables.) I'll keep chugging at it til I can get it. This is way cleaner than using multiple sub-queries.
ASKER
Ok, JimFive, I think using your statement and cleaning it up a little works.
FYI - I made a slight change:
select studentid, studentname, degree_major, degree_degreelevel, institute_name
FROM
(select studentid, max(instituteID), max(degree_graddate)
from student inner join degree on student.studentid = degree.studentid
inner join institute on student.studentid = institute.studentid) t
inner join degree on t.student_id = degree.student_id and degree.Degree_ID= t.Degree_ID
inner join institute on t.studentid = institute.student_id and t.institute_id = institute.institute_id
WHERE (YEAR(Degree.Degree_GradDa te) >= YEAR(GETDATE()))
Without 'and degree.Degree_ID= t.Degree_ID' duplicate records were returned. So I've expanded on this statement and it works.
Thanks a bunch
FYI - I made a slight change:
select studentid, studentname, degree_major, degree_degreelevel, institute_name
FROM
(select studentid, max(instituteID), max(degree_graddate)
from student inner join degree on student.studentid = degree.studentid
inner join institute on student.studentid = institute.studentid) t
inner join degree on t.student_id = degree.student_id and degree.Degree_ID= t.Degree_ID
inner join institute on t.studentid = institute.student_id and t.institute_id = institute.institute_id
WHERE (YEAR(Degree.Degree_GradDa
Without 'and degree.Degree_ID= t.Degree_ID' duplicate records were returned. So I've expanded on this statement and it works.
Thanks a bunch
Select * from Students
where Exists (Select * from Institute Where Students.StudentID = Institute.StudentID and Year(Institute.Institute_G