question

Cathy Brashear avatar image
Cathy Brashear asked

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                    
sql-server-2008aggregates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

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            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered
            
             (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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cathy Brashear avatar image
Cathy Brashear answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.