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)

          )                    
                 
     )</pre>                    

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

more ▼

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

avatar 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

avatar image

Kristen ♦
2.2k 7 11 14

(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

avatar image

Kristen ♦
2.2k 7 11 14

(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

avatar image

Kristen ♦
2.2k 7 11 14

(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

avatar image

Cathy Brashear
2 2 2 3

(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.

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:

x2076
x70

asked: Nov 23, 2009 at 12:43 PM

Seen: 2265 times

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

Copyright 2016 Redgate Software. Privacy Policy