开发者

SQL - Selecting with WHERE that applies to multiple rows

I am at a loss on how to write this query or even how to explain it - basically I am trying to have a WHERE clause apply to multiple rows.

I have a table like so:

RuleID  QuestionID  AnswerID
=================================
1       1100        1105
1       1200        1205
1       1300        1305
2       1100        1105
2       1200        1开发者_运维百科206
2       1300        1305

I am wondering how I can get all unique RuleIDs that have specific questions/answers.

For example, something like this, but obviously this returns no results as it's checking every where clause on the same row.

select DISTINCT RuleID FROM table 
where (QuestionID=1100 and AnswerID=1105)
and (QuestionID=1200 and AnswerID=1205)
and (QuestionID=1300 and AnswerID=1305)


Try this simple change

select DISTINCT RuleID FROM table 
where (QuestionID=1100 and AnswerID=1105)
or (QuestionID=1200 and AnswerID=1205)
or (QuestionID=1300 and AnswerID=1305)


Based on the way the question is written (which I am not sure reflects what you "really" want, by your own admission...) I think what you want is:

select DISTINCT RuleID FROM table 
where (QuestionID=1100 and AnswerID=1105)
OR (QuestionID=1200 and AnswerID=1205)
OR (QuestionID=1300 and AnswerID=1305)

If that doesn't work, please clarify.

[edit] others have also seized on the "OR" option, beyond that I am noticing a pattern that you might be able to exploit. Are all 'answerID' ranged by their 'questionID' ? (The available answers for Question XX00 are XX01, XX02, ...XX99). If this is the case then you can drop the questionID from your where clause since that value can be assumed by the AnswerID. This can allow you to use an IN clause like:

select DISTINCT RuleID FROM table 
where AnswerID IN (1105,1205,1305)


I'm not really following you, but I think you want something like this:

select DISTINCT RuleID FROM table 
where QuestionID IN (values)

you can also insert another select in the IN caluse:

select DISTINCT RuleID FROM table 
where QuestionId in (SELECT ...)


select DISTINCT RuleID FROM table 
where (QuestionID=1100 and AnswerID=1105
or (QuestionID=1200 and AnswerID=1205)
or (QuestionID=1300 and AnswerID=1305)

or am I missing something?


Is this the sort of thing your looking for

SELECT DISTINCT RuleID 
FROM table
WHERE QuestionID IN (1100,1200,1300)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜