By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Query across Multiple Tables MAX()

P: 2
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.Addr ess.City,dbo.Address.State, dbo.Address.Zip,dbo.Class.Year,dbo.ClassResult.Cla ssResultStatusID, dbo.Class.SemesterID,dbo.Location.LocationName, dbo.Institution.InstName

FROM dbo.Person INNER JOIN dbo.Person_Address ON dbo.Person.PersonID = dbo.Person_Address.PersonID INNER JOIN dbo.Address ON 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.InstitutionID

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.Semest erID, dbo.Class.Year,dbo.ClassResult.ClassResultStatusID , dbo.Location.LocationName,dbo.Institution.InstName

Having (dbo.Class.Year= dbo.Class.Year and dbo.Class.SemesterID = Class.SemesterID)and count()> 1 and dbo.Location.LocationName = dbo.Location.LocationName and count()> 1 and dbo.Institution.InstName = dbo.Institution.InstName and count()> 1 and ((NOT (dbo.ClassResult.ClassResultStatusID = 8)) and count()> 1 OR (NOT (dbo.ClassResult.ClassResultStatusID = 10)) and count()> 1 OR (NOT (dbo.ClassResult.ClassResultStatusID = 13))and count()> 1 OR (NOT (dbo.ClassResult.ClassResultStatusID = 12)) and count()> 1OR (NOT (dbo.ClassResult.ClassResultStatusID = 4))and count()> 1)

ORDER BY dbo.Person.LastName, dbo.Person.FirstName
Nov 23 '09 #1
Share this Question
Share on Google+
1 Reply


P: 2
Have cleaned it up
SELECT MAX(ClassResult.ClassID) AS LASTCLASS,
Person.PersonID,
Person.LastName,
Person.FirstName,
Address.Address1,
Address.City,
Address.State,
Address.Zip,
Class.Year,
ClassResult.ClassResultStatusID
FROM Person

INNER JOIN Person_Address ON Person.PersonID = Person_Address.PersonID
INNER JOIN Address ON Person_Address.AddressID = Address.AddressID
INNER JOIN ClassResult ON Person.PersonID = ClassResult.PersonID
INNER JOIN Class ON ClassResult.ClassID = Class.ClassID

GROUP BY Person.PersonID,
Person.LastName,
Person.FirstName,
Address.Address1,
Address.City,
Address.State,
Address.Zip,
Class.Year,
ClassResult.ClassResultStatusID
HAVING (ClassResult.ClassResultStatusID NOT IN (4, 8, 10, 12, 13))
ORDER BY Person.LastName, Person.FirstName
Nov 23 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.