finding a matches recursively

Expand Post »

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!

Similar Threads

finding area of something in Computer Science
Finding an IP address using Java (was: do u know?) in Java
Help finding modem driver for laptop in Windows NT / 2000 / XP
recursive findaverage function for Binary search tree in C
Finding Encrypted Files in Windows tips 'n' tweaks

zum

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2006

Permalink

May 16th, 2006

Re: finding a matches recursively

try this

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

May 17th, 2006

Re: finding a matches recursively

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

Thank you very much

zum

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: A simple question about hot key

Next Thread in MS SQL Forum Timeline: SQL problem - table names as variables

DaniWeb Message

Cancel Changes

User Name
Password