finding a matches recursively

[zum]

I have a table to store the answers of users of our sweepstake. The table that consists of 3 fields: the userid, the questionid and the answervalue.

Now I need to find the users with the most identical answers as a certain user.

For example I may have values like this (excerpt and simplified):

userid,questionid,answervalue
15,1,1
15,3,5
15,4,1
15,14,0
495,1,1
495,2,1
495,12,0
498,1,1
498,2,5
498,3,5
498,4,0

Now, if I search the closest matches for user 15 I'should find out that user 495 has one match (question 1) and user 498 has two (questions 1 and 3)

I need the SQL to get this information and don't know how.

Any ideas? It shouldn't be that difficualt but after a little too less of sleep my brain is kind of resting.

Thanks a lot!

[campkev]

try this

create proc getMatches(@user int)as
select count(a.userid) as NumberOfMatches, a.userid from tblAnswers a, tblanswers b
where a.questionid = b.questionid and a.answervalue = b.answervalue
and a.userid!= @user and b.userid = @user
group by a.userid

[zum]

Thanks of your hint I managed it even with some advanced queries to some foreign (or primary) tables.

Thank you very much