SQL Report

Topics: Developer Forum, User Forum
Jan 27, 2010 at 2:33 AM

I am trying to get a report of the tests out of the SLK.  I need to see the user, the test name, question, and answer.  I can get all of this but I get it multiple times (in this case 5).  Hope that someone can help

 

SELECT     TOP (100) PERCENT dbo.UserItem.Name AS LearnerName, dbo.AssignmentItem.RootActivityId, dbo.AssignmentItem.Title, dbo.AttemptItem.AttemptStatus, 
                      dbo.AttemptItem.FinishedTimestamp AS FTS, dbo.AttemptItem.LearnerAssignmentId, dbo.AttemptItem.CompletionStatus, dbo.AttemptItem.TotalPoints AS TPts, 
                      dbo.AssignmentItem.PointsPossible AS PtsPos, dbo.InteractionItem.LearnerResponseBool AS LearnBool, dbo.InteractionItem.LearnerResponseString AS LearnString, 
                      dbo.InteractionItem.LearnerResponseNumeric AS LearnNum, CorrectResponseItem_1.ResponsePattern, dbo.InteractionItem.ResultState, 
                      dbo.InteractionItem.ActivityAttemptId, dbo.InteractionItem.InteractionIdFromCmi, dbo.InteractionItem.InteractionType, dbo.AssignmentItem.SPSiteGuid, 
                      dbo.AssignmentItem.SPWebGuid
FROM         dbo.UserItem INNER JOIN
                      dbo.AssignmentItem INNER JOIN
                      dbo.AttemptItem ON dbo.AssignmentItem.RootActivityId = dbo.AttemptItem.RootActivityId ON dbo.UserItem.Id = dbo.AttemptItem.LearnerId INNER JOIN
                      dbo.ActivityAttemptItem ON dbo.ActivityAttemptItem.AttemptId = dbo.AttemptItem.Id INNER JOIN
                      dbo.InteractionItem ON dbo.ActivityAttemptItem.Id = dbo.InteractionItem.ActivityAttemptId INNER JOIN
                      dbo.CorrectResponseItem AS CorrectResponseItem_1 ON CorrectResponseItem_1.InteractionId = dbo.InteractionItem.Id
ORDER BY LearnerName
In the SQL designer, The AssignmentITem and Attempt Item dont match with a key...the other items do
Feb 2, 2010 at 3:48 PM

Put "DISTINCT" after "Select":

 

SELECT   DISTINCT  TOP (100) PERCENT dbo.UserItem.Name AS LearnerName, dbo.AssignmentItem.RootActivityId, dbo.AssignmentItem.Title, dbo.AttemptItem.AttemptStatus, 
                      dbo.AttemptItem.FinishedTimestamp AS FTS, dbo.AttemptItem.LearnerAssignmentId, dbo.AttemptItem.CompletionStatus, dbo.AttemptItem.TotalPoints AS TPts, 
                      dbo.AssignmentItem.PointsPossible AS PtsPos, dbo.InteractionItem.LearnerResponseBool AS LearnBool, dbo.InteractionItem.LearnerResponseString AS LearnString, 
                      dbo.InteractionItem.LearnerResponseNumeric AS LearnNum, CorrectResponseItem_1.ResponsePattern, dbo.InteractionItem.ResultState, 
                      dbo.InteractionItem.ActivityAttemptId, dbo.InteractionItem.InteractionIdFromCmi, dbo.InteractionItem.InteractionType, dbo.AssignmentItem.SPSiteGuid, 
                      dbo.AssignmentItem.SPWebGuid
FROM         dbo.UserItem INNER JOIN
                      dbo.AssignmentItem INNER JOIN
                      dbo.AttemptItem ON dbo.AssignmentItem.RootActivityId = dbo.AttemptItem.RootActivityId ON dbo.UserItem.Id = dbo.AttemptItem.LearnerId INNER JOIN
                      dbo.ActivityAttemptItem ON dbo.ActivityAttemptItem.AttemptId = dbo.AttemptItem.Id INNER JOIN
                      dbo.InteractionItem ON dbo.ActivityAttemptItem.Id = dbo.InteractionItem.ActivityAttemptId INNER JOIN
                      dbo.CorrectResponseItem AS CorrectResponseItem_1 ON CorrectResponseItem_1.InteractionId = dbo.InteractionItem.Id
ORDER BY LearnerName