I have a need for processing some data in order to come up with some statistic. This is the scenario:
Each user can answer several questions from the question list. They pick their answers from a drop-down list, therefore the answer is predictable in terms of format.
Some users answer more or less than others
I would like to choose a specific user, and run a query on his answers. For example: I want to know how many other users answered the same questions as my user and their answers where the same. If my user answered "his name is 25", "his gender is male", and "his height is 6" then how many users are in the database that have their age as 25, their gender male, and their height as 6 ?
Maybe user x answered 5 questions, but as long as his 3 questions and answers match with my user's 3 questions and answers it's a hit. I want to discards users who answered anything less than what my user answered which means user who answered 2 questions are discarded.
Can MySQL achieve such query without cause heavy performance hits ? And if yes how can I construct such query ? Here is some basic tables I created to communicate the concept better.
CREATE TABLE Question ( QID INT, QTxt VARCHAR(150), QOrder INT); CREATE TABLE User ( UID INT, UserName VARCHAR(150)); CREATE TABLE ANSWER ( AID INT, ATxt VARCHAR(150), QID INT, USERID INT);
I'm using this MySQL query, it works close to what I want but the "IN" doesn't completely match the exact result I'm interested in.
SELECT * FROM Answer as Ans WHERE Ans.QID IN (SELECT QID FROM Answer where USERID=1) GROUP BY USERID