Pulling MAX() from a query with multiple tables joined

Question

question

Cathy Brashear asked Nov 23, '09

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-2008 aggregates

10 |1200

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

Answer 1 · 2009-11-23T13:55:45Z

Kristen answered Nov 23, '09

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

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

Answer 2 · 2009-11-23T15:39:13Z

Kristen answered Nov 23, '09

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

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

Answer 3 · 2009-11-23T15:45:22Z

Kristen answered Nov 23, '09

            
             (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

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

Answer 4 · 2009-12-01T14:07:42Z

Cathy Brashear answered Dec 1, '09

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

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

question