[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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!
0
dstjohnjr
Asked:
dstjohnjr
  • 7
  • 4
  • 3
  • +2
4 Solutions
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now