February 28, 2008 at 2:45 pm
Hi all,
I am having some problem with writing this SQL statement. I have 2 tables (T1. User, T2. User Test Result)
User Test Result table contains all user test result data. Fields contain (UserID, TestID, and so on)
If I want to get results from user who finished all Test (Note: Must finished all the test, let say there are a total of 4 test. User must finished all 4 test in order to have them show up from the report). How would i write that SQL.
For the following example, if i want to know who have finished all the test (T1 and T2). The result should only return userid 1 and 2. Hope this is more clear. How could i write the sql for this? Thanks
Example (User Test Result table):
UserID TestID
1 1
1 2
2 1
2 2
3 1
February 28, 2008 at 3:06 pm
THis select will get all the users that completed all the tests.
Select
user_id
From
user_test_results
Group By
user_id
Having
Count(test_id) = (Select count(*) From tests)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 3:15 pm
Jack's solution will work well if there's no chance to have duplicate test records. If there's a possibility that a user have dupes tests, then perhaps adding DISTINCT will do the trick. As in -
...
HAVING count(Distinct Test_ID) =
....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply