lawrence cyber lawrence cyber - 5 months ago 29
SQL Question

How to find best match of Disease from Symptoms?

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


I want to search user selected symptoms, which are array of symptoms id in disease-symptoms table and return top 5 matching disease IDs, based on, where most of the symptoms are matching with each disease.

For example, user select symptoms are 1,2,3 (cough,cold,headache)

DISEASE_T SYMPTOMS_T disease-symptoms
========= ========== ===============
1,malaria 1,cough 1,1
2,tuberculosis 2,cold 1,2
3,typhoid 3,headache 2,2
2,3
3,1
3,2
3,3


Any help will be appreciated.

Answer

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