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
Table 2: Symptoms
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%';
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