Am working on an application that helps user to enter symptoms that matches those existing in the database and gets disease(diagnosis).

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
==========  ==========  ========



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

Help will be appreciated.

  • In your example, is the correct answer 3 or 3 followed by 1,2? 3 is the only disease that matches all three symptoms, but should the result also contain partial matches? – Lennart Aug 25 '16 at 21:11
  • The output doesn't matter a lot but generating the right logic to get that disease after entering the input from the form.For instance if 1 and 2 entered then malaria is the result hope you get the hint here – quintumnia Aug 25 '16 at 22:23

Select diseases with the 3 symptoms:

select DISEASE_ID, count() from DiseaseSymptoms_T where SYMPTOM_ID in (1, 2, 3) group by DISEASE_ID having count() = 3 -- number of symptoms in the where

That will only list typhoid, because it has all three symptoms

If you want all the diseases with the symptoms 1 and 2

select DISEASE_ID, count() from DiseaseSymptoms_T where SYMPTOM_ID in (1, 2) group by DISEASE_ID having count() = 2 -- number of symptoms in the where

This last query will also show the disease typhoid, because it has the two symptoms in the where

If you want only the first 5 rows, the syntax depends on the engine. For SQL Server use SELECT TOP 5 DISEASE_ID... For MySQL add LIMIT 1 at the end of the query... For Informix use SELECT FIRST 5...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.