Michael Ortiz Michael Ortiz - 1 year ago 85
SQL Question

Select Multiple Different Values from Table and Evaluate them for a match in table

I'm having some trouble trying to figure out how to select multiple records with multiple values. Let me try to break it down.

I have two tables.

Table 1: Diseases

enter image description here

Table 2: Symptoms

enter image description here

What I want to do is when the user enters a symptom, it will go through the diseases table and find the matching symptom related to it and then it will return the disease that has the symptom given.

This is the SQL query I have so far:

select a.diseases_id, a.disease_name
from diseases a, symtoms b
where a.diseases_id = b.diseases_id
and b.symtom_description like '%Cough%' AND b.symtom_description like '%Sore throat%';

Now the problem is what this SQL query returns nothing, yet if I remove the AND statement it returns all the diseases that contain "Cough" in their symptoms, but that is not what I want. I should be able to select a bunch of symptoms and if a diseases has all the symptoms given and they match, it will return the disease. For the query that I posted, it should return "Gastroesophageal Reflux" since is the diseases that only contains Cough and Sore Throat.

Can anyone give me any pointers of how to make this work?


Answer Source

There is no row, which contains "Cough" AND "Sore throat", so of course you will get an empty result.

I would try something like this (untested):

select a.diseases_id, a.disease_name, b.symtom_description
from diseases a, symtoms b 
where a.diseases_id = b.diseases_id
and (b.symtom_description like '%Cough%' OR b.symtom_description like '%Sore throat%')
group by a.diseases_id
having count(a.diseases_id)=2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download