Click here to Skip to main content
15,846,211 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

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?
Updated 2-Oct-10 21:11pm

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

Change it to:
DID    Name
1     Maleria
2     Typhoid

SID    Name
1     Fever
2     Cold
3     Cough

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...
Share this answer
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.
Share this answer
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 head was somewhere else!

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900