|
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
(comments are locked)
|
|
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
(comments are locked)
|
|
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
(comments are locked)
|
(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
(comments are locked)
|
|
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 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 '09 at 02:31 PM
Kristen ♦
(comments are locked)
|
