Solved

Creating a SQL statement with a max in the select statement and a max in the sub-query

Posted on 2007-12-03
12
181 Views
Last Modified: 2010-03-19
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.  
0
Comment
Question by:VibertH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 20396679
How about a WHERE EXISTS

Select * from Students
where Exists (Select * from Institute Where Students.StudentID = Institute.StudentID and Year(Institute.Institute_GradDate) = Year(GetDate())
0
 

Author Comment

by:VibertH
ID: 20396756
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.  
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20396854
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
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:VibertH
ID: 20396920
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?

0
 

Author Comment

by:VibertH
ID: 20396939
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_ExpGradDate) >= YEAR(GETDATE()))
GROUP BY dg.Student_ID, dg.Degree_ExpDegree, dg.Degree_Program


Something more like this.
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20397498
whate is degree_retdate?
0
 

Author Comment

by:VibertH
ID: 20397540
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_ExpGradDate) >= 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.
0
 
LVL 15

Accepted Solution

by:
JimFive earned 225 total points
ID: 20397738
select studentid, studentname, degree_major, degree_degreelevel, degree_GradDate, 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_gradDate = t.Degree_GradDate
inner join institute on t.studentid = institute.student_id and t.institute_id = institute.institute_id

--
JimFive
0
 

Author Comment

by:VibertH
ID: 20397921
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.
0
 

Author Comment

by:VibertH
ID: 20397947
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.
0
 

Author Comment

by:VibertH
ID: 20407281
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.
0
 

Author Comment

by:VibertH
ID: 20407389
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_GradDate) >= 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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question