Click here to Skip to main content
15,850,750 members
Please Sign up or sign in to vote.
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
Updated 2-Oct-10 21:11pm
v2

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
 
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!
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...
 
Share this answer
 

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