x

Pulling MAX() from a query with multiple tables joined

I am new to sql and have not been able to resolve this issue. I have a query that shows me the information on students we serve. The query pulls all of their classes and the status results as well as contact information. I need to query down to the LAST class taken and the students status. If I take out the following fields I get the correct return but I have to have all the information

                    
dbo.Class.Year,                    
dbo.ClassResult.ClassResultStatusID,                    
dbo.Class.SemesterID,                    
dbo.Location.LocationName,                    
dbo.Institution.InstName                    
                    
SELECT   MAX(dbo.ClassResult.ClassID)AS LASTCLASS,                    
            dbo.Person.PersonID,                    
            dbo.Person.LastName,                    
            dbo.Person.FirstName,                    
            dbo.Address.Address1,                    
            dbo.Address.City,                    
            dbo.Address.State,                    
            dbo.Address.Zip,                    
            dbo.Class.Year,                    
            dbo.ClassResult.ClassResultStatusID,                    
            dbo.Class.SemesterID,                    
            dbo.Location.LocationName,                    
            dbo.Institution.InstName                     
                    
FROM    FROM                    dbo.Person                    
INNER JOIN dbo.Person_Address ON dbo.Person.PersonID= dbo.Person_Address.PersonID                    
INNER JOIN dbo.AddressON dbo.Person_Address.AddressID = dbo.Address.AddressID                    
INNER JOIN dbo.ClassResult ON dbo.Person.PersonID  = dbo.ClassResult.PersonID                    
INNER JOIN  dbo.Class ON dbo.ClassResult.ClassID  = dbo.Class.ClassID                    
INNER JOIN dbo.V_ClassResultStatusID                     
     ON dbo.ClassResult.ClassResultStatusID = dbo.ClassResult.ClassResultStatusID                    
INNER JOIN dbo.V_SemesterID  ON dbo.Class.SemesterID  = dbo.Class.SemesterID                    
INNER JOIN dbo.Location ON dbo.Class.LocationID= dbo.Location.LocationID                    
INNER JOIN dbo.Institution ON dbo.Person.InstitutionID = dbo.Institution.ID                    
                    
Group by dbo.Person.PersonID, dbo.Person.LastName, dbo.Person.FirstName,                    
         dbo.Address.Address1, dbo.Address.City, dbo.Address.State,                     
         dbo.Address.Zip, dbo.Class.SemesterID, dbo.Class.Year,                    
         dbo.ClassResult.ClassResultStatusID,                    
         dbo.Location.LocationName, dbo.Institution.InstName                    
Having                       
         (dbo.ClassResult.ClassResultStatusID NOT IN (4, 8, 10, 12, 13)                    
                    
             )                    
                    
        )

ORDER BY dbo.Person.LastName, dbo.Person.FirstName
more ▼

asked Nov 23, 2009 at 12:43 PM in Default

Cathy Brashear gravatar image

Cathy Brashear
2 2 2 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

This also looks dubious:

            
INNER JOIN dbo.V_ClassResultStatusID             
     ON dbo.ClassResult.ClassResultStatusID             
      = dbo.ClassResult.ClassResultStatusID            
INNER JOIN dbo.V_SemesterID              
        ON dbo.Class.SemesterID            
         = dbo.Class.SemesterID            

both of those will always be true ...

... and these


dbo.Class.Year
= dbo.Class.Year
and dbo.Class.SemesterID
= Class.SemesterID)
and dbo.Location.LocationName
= dbo.Location.LocationName
and dbo.Institution.InstName
= dbo.Institution.InstName
more ▼

answered Nov 23, 2009 at 03:39 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

Your HAVING clause looks dangerous! The AND or OR statements will conflict, and you need to use parentheses to clearly isolate the ANDs and ORs

This may not be the answer to your question, but it may help in other ways

EDIT: You've re-edited your question to remove the ambiguity of OR / AND

more ▼

answered Nov 23, 2009 at 01:55 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
            
             (NOT             
                 (dbo.ClassResult.ClassResultStatusID = 8)            
             )             
             OR            
             (NOT (dbo.ClassResult.ClassResultStatusID = 10)            
             )             
             OR             
             (NOT (dbo.ClassResult.ClassResultStatusID = 13)            
             )            
             OR             
             (NOT (dbo.ClassResult.ClassResultStatusID = 12)            
             )            
             OR             
             (NOT (dbo.ClassResult.ClassResultStatusID = 4)            
             )            

I haven't given it a lot of brain-thought, but could this be simplified to:

            
    dbo.ClassResult.ClassResultStatusID NOT IN (4, 8, 10, 12, 13)            

In reality your OR tests will always be true, but perhaps my suggestion is what you actually meant?

EDIT: You've re-edited your question to remove this complexity, but no comment as to why

more ▼

answered Nov 23, 2009 at 03:45 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

I have edited the query to give me the least amount of data needed but am still unable to get only one classid per PersonId

more ▼

answered Dec 01, 2009 at 02:07 PM

Cathy Brashear gravatar image

Cathy Brashear
2 2 2 3

Can you edit your question to add the query you are currently using (or ask a fresh question), then we can take a look :)
Dec 01, 2009 at 02:31 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x59

asked: Nov 23, 2009 at 12:43 PM

Seen: 2082 times

Last Updated: Nov 23, 2009 at 04:28 PM