SQL Query to return distinct UserID from combination of 3 tables

I am building the following query that uses 3 tables.

SELECT     XYZQuiz.Name AS 'Course', XYZQuiz.Points AS 'Course Points', XYZQuizUserLog.Grade, XYZQuizUserLog.Points, XYZUser.FirstName, XYZUser.LastName, XYZUser.Email, XYZQuizUserLog.DateEntered, XYZUser.UserId
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId

I would like for it NOT to return duplicate UserID values (distinct).  I'm sure I have to use DISTINCT somewhere in there, but need some expert guidance on where.

TIA!
dstjohnjrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
You can use distinct for the entire query result row like below.

SELECT  Distinct   XYZQuiz.Name AS 'Course', XYZQuiz.Points AS 'Course Points', XYZQuizUserLog.Grade, XYZQuizUserLog.Points, XYZUser.FirstName, XYZUser.LastName, XYZUser.Email, XYZQuizUserLog.DateEntered, XYZUser.UserId
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
0
digital_thoughtsCommented:
One option is:

SELECT     DISTINCT
      XYZQuiz.Name AS 'Course',
      XYZQuiz.Points AS 'Course Points',
      XYZQuizUserLog.Grade,
      XYZQuizUserLog.Points,
      XYZUser.FirstName,
      XYZUser.LastName,
      XYZUser.Email,
      XYZQuizUserLog.DateEntered,
      XYZUser.UserId
FROM
      XYZQuiz INNER JOIN XYZQuizUserLog
            ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID
      INNER JOIN XYZUser
            ON XYZQuizUserLog.UserID = XYZUser.UserId
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YveauCommented:
Distinct will only filter out result lines that are exactly the same, so not do as you would like ...
Make sure to use a group by (XYZUser.UserId) clause in the query ... but you have to agregate all other fields in the resultset as well !

Hope this helps ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imitchieCommented:
SELECT    
XYZQuiz.Name AS 'Course',
XYZQuiz.Points AS 'Course Points',
XYZQuizUserLog.Grade,
XYZQuizUserLog.Points,
XYZQuizUserLog.DateEntered,
XYZUser.UserId,
XYZUser.FirstName,
XYZUser.LastName,
XYZUser.Email
FROM XYZQuiz
INNER JOIN XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID
CROSS JOIN
(SELECT TOP 1 * FROM XYZUser WHERE XYZQuizUserLog.UserID = XYZUser.UserId) XYZUser

0
imitchieCommented:
So if a User takes a Quiz more than once, which Grade and Point do you want to show?
0
YveauCommented:
... or if a User takes two courses, which one would you like to see ?
0
dstjohnjrAuthor Commented:
That is a good point - the Grade.

There are only two Grade options - P & F (Pass & Fail).

If the user takes the quiz 5 times, fails 4 and passes once, then we need to see only 1 record for Grade F and 1 record for Grade P.  Would we group by Grade then?

Tia!
0
dstjohnjrAuthor Commented:
Getting right down to it - here are my specs:

1.      Total Number of courses completed (only reporting each course once per person)
2.      Total Number of courses passed (only reporting each course once per person)
3.      Average number of courses passed for a user

Does that help?
0
imitchieCommented:
SELECT     XYZQuiz.Name AS 'Course', XYZQuiz.Points AS 'Course Points', XYZQuizUserLog.Grade, MAX(XYZQuizUserLog.Points) AS Points, XYZUser.FirstName, XYZUser.LastName, XYZUser.Email, MAX(XYZQuizUserLog.DateEntered) AS DateEntered, XYZUser.UserId
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
GROUP BY XYZQuiz.Name AS 'Course', XYZQuiz.Points AS 'Course Points', XYZQuizUserLog.Grade, XYZUser.FirstName, XYZUser.LastName, XYZUser.Email, XYZUser.UserId
0
YveauCommented:
group by the combination of fields that should be unique in the resultset
... so grade and would be in there as well.

Hope this helps ...
0
imitchieCommented:
1.      Total Number of courses completed (only reporting each course once per person)
2.      Total Number of courses passed (only reporting each course once per person)
3.      Average number of courses passed for a user

AND

If the user takes the quiz 5 times, fails 4 and passes once, then we need to see only 1 record for Grade F and 1 record for Grade P.  Would we group by Grade then?

Do not seem to match... one says show F and P (both) the other says ONE COURSE per person
0
dstjohnjrAuthor Commented:
This will probably need to be broken out into separate queries.  Let's redefine:

1.      Total Number of courses completed (only reporting each course once per person)
This is a Grand Total (count) of the total number of courses completed, whether they passed or failed, and only counting each once once per UserID

Will start as:
SELECT count(*) as 'Total Courses Completed' ...

2.      Total Number of courses passed (only reporting each course once per person)
Count number of courses passed (Grade=P) each course once per person)
SELECT count(*) as 'Total Courses Passed' ...
WHERE Grade = 'P'

3.      Average number of courses passed for a user
Should be self explanatory.

Thanks so much for all of your replies!  EE is a Godsend!
0
imitchieCommented:
3.      Average number of courses passed for a user

Averaged across what?  Is this an average for all users?
0
imitchieCommented:
Select course, count(*) as completed, sum(passed) as passed  
FROM
(select xyzquiz.quizid, min(xyzquix.name) as course, xyz.userid, max(
 case when xyzquizuserlog.grade  = 'P' then 1 else 0 end) as passed
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
) x group by course
0
imitchieCommented:
Select course, count(*) as completed, sum(passed) as passed,
  1.0 * count(*) / count(distinct userID) as AveragePerUser
FROM
(select xyzquiz.quizid, min(xyzquix.name) as course, xyz.userid, max(
 case when xyzquizuserlog.grade  = 'P' then 1 else 0 end) as passed
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
) x group by course
0
dstjohnjrAuthor Commented:
Thanks for the assistance experts.  Sorry for the delay in reply.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.