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)