Finding exact matches to a requested set of values

Question

Hi I'm facing a challenge. There is a table progress.

User_id | Assesment_id
-----------------------
1       | Test_1
2       | Test_1
3       | Test_1
1       | Test_2
2       | Test_2
1       | Test_3
3       | Test_3

I need to pull out the user_id who have completed only Test_1 & test_2 (i.e User_id:2). The input parameters would be the list of Assesment id.

Edit:

I want those who have completed all the assessments on the list, but no others.
User 3 did not complete Test_2, and so is excluded.
User 1 completed an extra test, and is also excluded.
Only User 2 has completed exactly those assessments requested.

Hi Vijaykumar, That is the catch. I don't need the user who have completed additional assesment. I need only the users who have completed test_1 & Test_2. So, User_id:2 will be the only result. As User_id:1 have completed additional assesment, it should be ruled out.Thanks. — CRayen, Dec 03 '13 at 06:41
... you don't need `ROW_NUMBER()` for this. There are a number of questions of this type on this site...like [this answer I wrote long ago](http://stackoverflow.com/questions/6489224/how-do-i-find-the-user-that-has-both-a-cat-and-a-dog/6499897#6499897). — Clockwork-Muse, Dec 03 '13 at 12:13
@Clockwork-Muse that answer does not address the complexity of what the OP is asking. Check the response to Vijaykumar's first comment. — WarrenT, Dec 04 '13 at 01:57
@Warrant - Hmm, you're right, I missed the 'not everything' condition. Regardless, the basic premise of the question has likely shown up here (I'm certain I've seen one, but can't for the life of me recall when or what it was called)... — Clockwork-Muse, Dec 04 '13 at 09:31

score 0 · Answer 1 · answered Dec 03 '13 at 16:29

0

You don't need a complicated join or even subqueries. Simply use the INTERSECT operator:

select user_id from progress where assessment_id = 'Test_1'
intersect
select user_id from progress where assessment_id = 'Test_2'

answered Dec 03 '13 at 16:29

Ian Bjorhovde

10,916
1
28
25

Thanks Ian, As I had mentioned earlier, I need only the user_id who have completed exactly the Test_1 & test_2. In intersect it will retrieve user_id:1 who have completed test_3 also. Appreciate your response. – CRayen Dec 04 '13 at 07:37

score 0 · Answer 2 · answered Dec 04 '13 at 01:54

I interpreted your question to mean that you want users who have completed all of the tests in your assessment list, but not any other tests. I'll use a technique called common table expressions so that you can follow step by step, but it is all one query statement.

Let's say you supply your assessment list as rows in a table called Checktests. We can count those values to find out how many tests are needed.

If we use a LEFT OUTER JOIN then values from the right-side table will be null. So the test_matched column will be null if an assessment is not on your list. COUNT() ignores null values, so we can use this to find out how many tests were taken that were on the list, and then compare this to the number of all tests the user took.

with x as
(select count(assessment_id) as tests_needed
   from checktests
),
dtl as
(select p.user_id, 
        p.assessment_id  as test_taken,
        c.assessment_id  as test_matched
   from progress p
   left join checktests c   on p.assessment_id = c.assessment_id
),
y as
(select user_id, 
        count(test_taken)    as all_tests,
        count(test_matched)  as wanted_tests  -- count() ignores nulls
   from dtl
   group by user_id
)
select user_id
  from y
  join x     on y.wanted_tests = x.tests_needed 
  where         y.wanted_tests = y.all_tests ;

Hm, maybe you can give a 'condensed' version as well (with things like `HAVING` clauses, etc). For other readers, note that `checktests` doesn't have to be a permanent table (it probably shouldn't be - concurrent query access is problematic); it can be a CTE (probably requires a dynamic statement, though), or a temp table. — Clockwork-Muse, Dec 04 '13 at 09:36
Yes, in this example `checktests` can be any valid [table reference](http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafztabref.htm) — WarrenT, Dec 05 '13 at 22:29

Finding exact matches to a requested set of values

2 Answers2