MS SQL

finding a matches recursively

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2006

Posts: 3

Reputation: zum is an unknown quantity at this point

Solved Threads: 0

zum

Offline

Newbie Poster

finding a matches recursively

May 16th, 2006

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!

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: finding a matches recursively

May 16th, 2006

try this

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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