Solved

SQL Query to return distinct UserID from combination of 3 tables

Posted on 2007-12-04
16
459 Views
Last Modified: 2010-08-05
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
Comment
Question by:dstjohnjr
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20407196
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
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 125 total points
ID: 20407201
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20407212
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 375 total points
ID: 20407235
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20407261
So if a User takes a Quiz more than once, which Grade and Point do you want to show?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407268
... or if a User takes two courses, which one would you like to see ?
0
 

Author Comment

by:dstjohnjr
ID: 20407275
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
 

Author Comment

by:dstjohnjr
ID: 20407287
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 25

Expert Comment

by:imitchie
ID: 20407291
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20407298
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20407303
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
 

Author Comment

by:dstjohnjr
ID: 20407376
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20407404
3.      Average number of courses passed for a user

Averaged across what?  Is this an average for all users?
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 375 total points
ID: 20408084
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 375 total points
ID: 20408104
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
 

Author Closing Comment

by:dstjohnjr
ID: 31412713
Thanks for the assistance experts.  Sorry for the delay in reply.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now