[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Query to return distinct UserID from combination of 3 tables

Posted on 2007-12-04
16
Medium Priority
?
475 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 1500 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
 
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 1500 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 1500 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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