CODEWITHSUNDEEP
sqlsql-server
Ringo64
Ringo64

Reputation: 13

Multiple rows get only specific values

Feel like this should be a rather simple problem yet, I'm struggling to find the solution.

We have three tables to create a Question Answer system. One is the question, other is answer and then the third is finally where we store the user's selection.

Question table

QuestionID Question 
1          What is your favorite color?
2          Where were you born?

Answer table

AnswerID QuestionID Answer
1        1          Blue
2        1          Green
3        1          Yellow
4        2          USA
5        2          Africa

Answer stored table

AnswerStoreID QuestionID AnswerID UserID
1             1          1        1
2             1          2        1
3             2          4        2
4             2          5        2
5             1          1        3

I want to find the UserID that answered QuestionID 1 as AnswerID 1 AND QuestionID 2 as AnswerID 4.

Thought it would be simple like this

SELECT UserID
FROM Question Q
INNER JOIN Answer A ON A.QuestionID = A.QuestionID
INNER JOIN AnswerStore AS ON AS.AnswerID = A.AnswerID
WHERE (AS.AnswerID = 1 AND AS.QuestionID = 1)
AND (AS.AnswerID = 2 AND AS.QuestionID = 4)

That renders nothing though. When replacing the AND between the two where statements with an OR gets results that don't have both those answers though which is not desired either. I want only those users who answered both of these questions.

I then did a query with some various joins to do a query per question but feel that is too complicated and heavy for this problem and I'm overthinking it. Is there an easier solution to this problem?

Upvotes: 0

Views: 34

Answers (2)

Stephen Wuebker
Stephen Wuebker

Reputation: 189

You are really looking at two sets of data, UserIDs that answered QuestionID 1 as AnswerID 1, and UserIDs that answered QuestionID 2 as AnswerID 4. So you can join the sets together to find UserIDs that are in both sets of data:

SELECT UserID
FROM AnswerStore as1 INNER JOIN AnswerStore as2 ON as1.UserID = as2.UserID
AND as1.QuestionID = 1 AND as1.AnswerID = 1
AND as2.QuestionID = 2 AND as2.AnswerID = 4

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15951

---- Edit ----

Actually, you don't even need the JOINs in your original query:

SELECT t.UserID
FROM AnswerStore AS t
WHERE (t.AnswerID = 1 AND t.QuestionID = 1)
   OR (t.AnswerID = 2 AND t.QuestionID = 4)
GROUP BY t.UserID
HAVING COUNT(*) = 2

---- Original Full Answer ----

This is actually a fairly common question, that appears a couple times a week. Unfortunately, it is really hard to formulate a repeatable/searchable question to reference for it.

SELECT UserID
FROM Question Q
INNER JOIN Answer A ON A.QuestionID = A.QuestionID
INNER JOIN AnswerStore AS ON AS.AnswerID = A.AnswerID
WHERE (AS.AnswerID = 1 AND AS.QuestionID = 1)
   OR (AS.AnswerID = 2 AND AS.QuestionID = 4)
GROUP BY UserID
HAVING COUNT(*) = 2

The general form is:

SELECT A.a_id
FROM A
INNER JOIN B ON A.a_id = B.a_id
WHERE B.something IN ([list])
GROUP BY a_id
HAVING COUNT(*) = [length of list]
-- or in cases where B matches may be non-unique
-- HAVING COUNT(DISTINCT B.something) = [length of list]

Upvotes: 2

Related Questions