I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar questions other users answered. For Example : Let's say I want to see how many people in my Answer table answered age as 30 like Alex. So how can I join these two tables based on User.USERID="1" ? My expected result should be 1 since There is only one user "Tom" who is 30 years old and is "M" Male.
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); INSERT INTO User VALUES (1, 'Alex'), (2, 'Sarah'), (3, 'Tom'); INSERT INTO Question VALUES (1, 'Age', 1), (2, 'Gender', 2), (3, 'Weight',3), (4, 'Height',4); INSERT INTO Answer VALUES (1, '30', 1 , 1), (2, '27', 1,2), (3, '30', 1,3), (4, 'M', 2,1), (5, 'F', 2,2), (6, 'M', 2,3), (7, '61', 4,1), (8, '165', 3,1);
This is how I did it and it doesn't work :(
SELECT * FROM Answer JOIN Question ON Answer.QID IN (SELECT Answer.QID FROM Answer WHERE Answer.USERID=3) AND Answer.ATxt IN (SELECT Answer.ATxt FROM Answer WHERE Answer.USERID=3)