February 7, 2012 at 3:05 pm
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
February 7, 2012 at 3:41 pm
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2012 at 3:54 pm
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)
February 8, 2012 at 6:20 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply