Return user only if all rows match

Question

Return user only if all rows match

TcW_1978

SSCrazy

Points: 2018
More actions
February 7, 2012 at 3:05 pm

#267962

I have a table with the user, username, a table with questions and a table users' answers
Let's say :
[col id]
1 John
2 Max
[question table]
[col id] [desc]
1 question 1
2 question 2
[answer table]
[col id] [fk_user] [fk_question] [answer]
1 1 1 1
2 1 2 1
3 2 1 1
4 2 2 3
I want the user only if he answered 1 to both questions. I pass a XML string to my stored proc and create a temp. table that looks like this :
[temp table]
[question] [answer]
1 1
2 1
I try to make a query to get the user if he answered "1" to both questions but can't seem to get it right, if I do an INNER JOIN on the temp table I always get the users that answered "1" to one of the questions.
Any help, hint or comment would be greatly appreciated.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

I'm guessing that this is a simplified version of what you are trying to do - so we run the risk of my answer not being sufficient for your real needs. But here goes (untested):

;with SelectedUsers as (select userid from answertable where answer = 1 group by userid having count(1) = 2)

select u.User

from usertable u join SelectedUsers on u.Id = SelectedUsers.UserId

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Chad Crawford SSChampion Points: 11610 More actions · Answer 2

Similar to what Phil posted and using the same theory, assuming structure and data like this:CREATE TABLE usertable (colid int, Username varchar(50))

INSERT INTO usertable VALUES (1, 'John')

INSERT INTO usertable VALUES (2, 'Max')

CREATE TABLE Questions (colid int, description varchar(50))

INSERT INTO Questions VALUES (1, 'question1')

INSERT INTO Questions VALUES (2, 'question2')

CREATE TABLE Answers (colid int, UserID int, QuestionID int, Answer int)

INSERT INTO Answers VALUES (1,1,1,1)

INSERT INTO Answers VALUES (2,1,2,1)

INSERT INTO Answers VALUES (3,2,1,1)

INSERT INTO Answers VALUES (4,2,2,3)

CREATE TABLE ParsedXML (QuestionID int, Answer int)

INSERT INTO ParsedXML VALUES (1,1)

INSERT INTO ParsedXML VALUES (2,1)

This query flexibly takes your XML table and returns matching users:SELECT UserID

FROM Answers

INNER JOIN ParsedXML ON Answers.QuestionID = ParsedXML.QuestionID AND Answers.Answer = ParsedXML.Answer

GROUP BY UserID

HAVING COUNT(*) = (SELECT COUNT(*) FROM ParsedXML)

TcW_1978 SSCrazy Points: 2018 More actions · Answer 3

Phil Parkin : well, the tables are simplified but it's almost as is in terms of structure.

Thanks guys, I just added the SELECT COUNT(*) = ... at the end of my query and everything started working as I wanted it. Magic!

Thanks again.