Dynamically generate SQL query based on selection in ASP.net

Question

I have a table 1:

enter image description here

and table 2 :

enter image description here

Each question can have multiple choices (ex: questionId 1 has two choices choiceId 1 and 2).

I have a form which will allow users to select multiple choices like this:

enter image description here

There is also a table for answers:

enter image description here

Now my question is: I want to form a SQL query based on the selection and show the count of selected answers.(ex: I want count of answers having gender as female and education as grade 1). How do I do that?

I tried something like this:

select * from answers where (questId =2 and choiceIndex='2.0000') and (questId =3 and choiceIndex='1.0000') and someId=72

This query does not give me the correct count as same choiceIndex can exists for a different choice.

How can I achieve this? Also user can selected many choices from the form so is there any way by which I can dynamically generate my SQL query?

Eidt

Thanks @Marc Gravell for the solution. It helped a lot. Now once I achieved this I want to include arithmetic operators in my query. (ex: I want count of answers having gender as female + education as grade 1 - education as grade3).

Is there a way by which I can include arithmetic operators(+, -, *, /, %, count, average) in my query?

Scenario:I have designed a form in which a user will generate formula from database values. (ex:user will select choice1[female] + choice2[grade 1] * choice3[grade3] and generate formula will be saved in DB for further use)

I tried like this:

 select ((select COUNT(*) from answers where (questId =2 and answer='1.0000') and someId=72) +
 (select COUNT(*) from answers where (questId =3 and answer='1.0000') and someId=72))

In this query I am trying to perform add operation on two select statements. In the same way can I do *,/,percentage etc?

Is there a better way to do this?

re the edit; yes the database can perform arithmetic operations, but it would require numeric inputs - it isn't clear what you mean exactly in your "like this" example — Marc Gravell
– Marc Gravell, Commented Apr 7, 2015 at 10:12
In that query I am trying to add count of 2 select queries. Same way I would like to perform other arithmetic operations. Is there any better way to do this? — Arti
– Arti, Commented Apr 7, 2015 at 10:18
Yes, it works. Just wondering if there's any other better way to do that. — Arti
– Arti, Commented Apr 7, 2015 at 12:03
no, that looks about right; you could also use: select count(1) from (query 1) union [all] (query 2) union [all] (query 3), but what you have is fine — Marc Gravell
– Marc Gravell, Commented Apr 7, 2015 at 12:05

Marc Gravell · Accepted Answer · 2015-04-07 07:49:30Z

1

choiceIndex is never going to be 2.0000 and 1.0000 at the same time; I assume, therefore, that you mean:

select * from answers where (
    (questId=2 and choiceIndex='2.0000') or
    (questId=3 and choiceIndex='1.0000')
) and someId=72

Note that if you are building a query based on input values, you should probably parameterize it, so it ends up as:

select * from answers where (
    (questId=@questId0 and choiceIndex=@choiceIndex0) or
    (questId=@questId1 and choiceIndex=@choiceIndex1)
) and someId=@someId

having added parameters with values:

@questId0 => 2
@questId1 => 3
@choiceIndex0 => '2.0000'
@choiceIndex1 => '3.0000'
@someId => 72

answered Apr 7, 2015 at 7:49

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arti Over a year ago

thanks for a quick reply. It helped a lot. I have one more query, I have updated my question. Please take a look.

Collectives™ on Stack Overflow

Dynamically generate SQL query based on selection in ASP.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related