Am working on an application that helps user to enter symptoms and gets disease.
I have the following database table with information about symptoms, diseases, and disease-symptoms which associates the two tables, as shown below:
DISEASE_T SYMPTOMS disease-symptoms
========== ========== ========
DISEASE_ID SYMPTOM_ID DISEASE_ID
VALUE VALUE SYMPTOM_ID
DISEASE_T SYMPTOMS_T disease-symptoms
========= ========== ===============
1,malaria 1,cough 1,1
2,tuberculosis 2,cold 1,2
3,typhoid 3,headache 2,2
Simple query of your direct Disease-Symptoms table based on the symptoms you are interested in qualifying against. The group by will give one record per disease no matter how many actual symptoms qualify. Finally, the ORDER by in DESCENDING order will put the most matched symptoms to the top of the list.
Now, simple join to your symptoms and Disease to get the "value" descriptors for final read context output.
select DS.Disease_ID, D.Value as Disease, group_concat( S.Value SEPARATOR ',' ) as Symptoms, count(*) as SymptomsMatching from Disease-Symptoms DS JOIN Symptoms_T S on DS.Symptom_ID = S.Symptom_ID JOIN Disease_T D on DS.Disease_ID = D.Disease_ID where DS.Symptom_ID IN ( 1, 2, 3 ) group by DS.Disease_ID order by count(*) desc, D.Value