cross-section select

I have table pool with columns:
USERID,QUESTIONID,ANSWERID
I have about 50 questions in table and each question can have couple
answers.
For example:
USERID QUESTIONID ANSWERID
----
1 1 2
1 2 1
1 3 5
.....
2 1 6
2 2 1
2 3 4
...and so on
Now I have to select all user id's which had answered with answerID=2 to
first question
and with answerID=1 to second question and so on...
So, I have array of questions: (1,2,4,5,8,12,17,18,20)
and
array of answers: (2,1,4,5,3,1,1,2,3)
Now, I have to find cross-section of users who has answered to required
questions to required answers.
Any idea?> array of answers: (2,1,4,5,3,1,1,2,3)
Ugh. Stop thinking about these in terms of arrays. They are not arrays!

> Any idea?
Canyou please provide better specs, because we don't know what "and so on"
means. Please see the following:
http://www.aspfaq.com/5006|||To Aaron and others who frequently contribute answers:

> http://www.aspfaq.com/5006
Often times when I post a question, I'm not looking for a solution to a
specific instance but more or less "is this a good idea". (See my recent
post on "Replace temp table with inline table-value function"). I try to
include as much information as is necessary to be clear about my problem.
In these cases, do you still want fully functional DDL, sample data, and
desired results? I'm not looking for a person to run through and test these
things, I'm simply looking for someone who has worked through a similar
issue and can say, "Yes, inline table-value functions are good for this" or
"No, here's why its bad and here's an alternative".
I'm not trying to undermine what you are requesting, because I understand
the value of what you are requesting, I simply want to clarify if that is a
blanket requirement (DDL, data, results) or a general requirement for
problems that need to be reproduced.
Thanks,
Mike|||> I'm not trying to undermine what you are requesting, because I understand
> the value of what you are requesting, I simply want to clarify if that is
> a blanket requirement (DDL, data, results) or a general requirement for
> problems that need to be reproduced.
No, I think you'll notice that I don't always post a link to that article,
only when it is relevant. Unfortunately, the information is included by
default, about 1% of the cases where it should be, so you might see the link
posted a lot. I think questions that are more general in nature (how do I
choose a primary key, should I use temp tables, what are the benefits of
identity vs. guid, etc) do not require any of this low-level detail, and you
won't be pressed for it, either.|||Hi Simon,
Please do post DDL, DML so that we can test our queries
I hope the Select statement at the end will solve your pupose
create table QuesAns(USERID int ,QUESTIONID int,ANSWERID int)
insert into QuesAns values( 1 , 1, 2 )
insert into QuesAns values( 1, 2 , 1 )
insert into QuesAns values( 1, 3 , 5 )
insert into QuesAns values( 2, 1 , 6 )
insert into QuesAns values( 2, 2 , 1 )
insert into QuesAns values( 2, 3 , 4 )
select * from QuesAns
-- An array(sorry all SQL standard supporters) can be easily replaced
by a Table like this
create TABLE CORRECTANS
(
QUESTIONID INT,
ANSWERID INT
)
INSERT INTO CORRECTANS VALUES(1,2)
INSERT INTO CORRECTANS VALUES(2,1)
SELECT QuesAns.USERID,COUNT(*) TotalCorrectAns FROM QuesAns ,
CORRECTANS
WHERE QuesAns.QUESTIONID = CORRECTANS.QUESTIONID
AND QuesAns.ANSWERID = CORRECTANS.ANSWERID
GROUP BY QuesAns.USERID
drop table QuesAns
DROP TABLE CORRECTANS
With warm regards
Jatinder Singh