Query for selected list of items

Question

0.00/5 (No votes)

See more:

Hi,

I am working with ASP.NET and sqlserver. The project consists of three layers namely UI,business and data access layer. In the UI layer i have listbox with multiple values get selected . i will take this values to the data access layer.

The database consists of diseases and symptoms. just like:

disease    symptom

malaria    fever
malaria    cold
typhoid    fever
typhoid    cough

If I select the symptoms as fever and cold, it should return malaria.
If I select the symptoms fever and cough, it should return typhoid.
If I select fever only, it should give malaria and typhoid.

Can anyone, please help in writing the query?

Posted 2-Oct-10 20:01pm

lepakshi.glr

Updated 2-Oct-10 20:11pm

Sandeep Mewara

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DaveAuld · Answer 1 · 2010-10-02T20:25:00

Solution 1

Select Distinct disease From 'theTable' Where symptom in ('fever','cough')

That should return a list of diseases which have the list of symptoms.

If you do not use distinct, you would return multiple records for the same disease.

Posted 2-Oct-10 20:25pm

DaveAuld

Updated 3-Oct-10 8:47am

v3

Comments

Sandeep Mewara 3-Oct-10 2:43am

Unique? or DISTINCT?
I guess unique is Oracle specific, not sure, you might share. :)

DaveAuld 3-Oct-10 14:46pm

Distinct......my head was somewhere else!

Sandeep Mewara · Answer 2 · 2010-10-02T20:39:00

Well, first of all your current Table structure does not support all scenarios and this kind of query results.

Change it to:

MstDiseases
DID    Name
1     Maleria
2     Typhoid


MstSymptoms
SID    Name
1     Fever
2     Cold
3     Cough

TrnDiseaseSymptom
TID   DID   SID
1      1     1
2      1     2
3      2     1
4      2     3

Now, I would like to try the SQL query yourself. It should be much simpler now. Try!
Hint: Use TrnDiseaseSymptom table to get the disease ID's...