I hope this is so simple I'm missing it... I have a table which contains a Prompt_ID field, an Answer_Text field and an Answer_ID. A user's answers are grouped by the Answer_ID when needed. The Prompt_ID is a foreign key to another table. There can be an infinite number of these IDs. The Answer_Text is a user's answer to a particular question.
My problem is that I need to be able to pull a recordset of, for example: WHERE Prompt_ID = 67 AND Answer_Text = 'yes' AND Prompt_ID = 39 AND Answer_Text = 'five' AND Prompt_ID = 192 AND Answer_Text = 'thisorthat'. You get the point. There can be many conditions.
BUT I can't AND condition these because they do not share the same row - a query gives me nothing because there is NO WAY that all three of these conditions exist within the same record... by definition it's three different records.
I have a feeling I need to include a select statement that yields the answer groups (the groups are actually also indexed on another containing index - there can be dozens of answer groups within one of these larger groups), but I can't make the leap to how to get all records with the same answer id to be treated as a single record.
So I think I need a way to aggregate all rows that share a common Answer_ID and then perform my tests against this derived row. Is there a simple way to do this... or am I stuck iterating or creating a temp table? Thanks.
Your question raises two possibilities, and both are worth discussion.
The first concerns an ambiguity in stating the problem. I don't think this is your case, but I'll include it as a starting point.
When writing SQL based on all the rows with A=X and A=Y, make sure you are answering the right question -- often the original requirement started out as "I want all the records with A=X, and I also want all the records with A=Y." I see this problem of ambiguity all the time, usually in more complex conditions where ORs and ANDs intermingle, sometimes without the necessary parentheses. In any case, this problem of translating I want this AND I also want that into WHERE this OR that is one of the seminal "a ha" moments in learning SQL.
select Answer_ID from yourTable where ( Prompt_ID = 67 AND Answer_Text = 'yes' ) OR ( Prompt_ID = 39 AND Answer_Text = 'five' ) OR ( Prompt_ID = 192 AND Answer_Text = 'thisorthat' )
You can see that a couple of your original ANDs have been replace with ORs and that parentheses have been added -- in this example, these parentheses are superfluous, since the priority of evaluating complex conditions is that ANDs take precedence over ORs (mnemonic: A before O).
The second possibility, and I think this is your case, is that you want the rows to be considered as a group and you want to see only groups that include rows with all those conditions. This is often encountered in job skill databases, where you are interested in candidates that have certain skills at certain levels, but you're only interested in candidates that have at least three of the matching conditions.
At first you might think that the solution involves WHERE EXISTS structures, but that way lies madness. Always think in terms of the final result, and you'll see that it's only the group that matters. So the HAVING clause comes into play.
select Answer_ID from yourTable where ( Prompt_ID = 67 AND Answer_Text = 'yes' ) OR ( Prompt_ID = 39 AND Answer_Text = 'five' ) OR ( Prompt_ID = 192 AND Answer_Text = 'thisorthat' ) group by Answer_ID having count(*) = 3
Every row will either meet one of the conditions, or not. If it does, it's included in the group. Once the groups have been formed, the number of rows in the groups is tested, and only groups that have three rows are kept. The Answer_ID values in the result are the Answer_IDs of the groups that had three rows meet the listed conditions, of which there were three, i.e. all of them.
If you had many conditions, and wanted at least three matched, it would be --
having count(*) >= 3
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.