many-to-many query

Question

I have following database structure,

CREATE TABLE IF NOT EXISTS `analyze` (
  `disease_id` int(11) NOT NULL,
  `symptom_id` int(11) NOT NULL
) ;


CREATE TABLE IF NOT EXISTS `disease` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ;


CREATE TABLE IF NOT EXISTS `symptom` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ;

EDIT: Sorry, I mean how do I identify the disease from inputted symptoms. Example: If I have symptom: fever and cough then I would have influenza. If I have symptom: sore throat and fever then I would have throat infection. The input are $symptom1, $symptom2, $symptom3, and so on.

Thank you.

Answer 1

SELECT disease_id
FROM analyze
GROUP BY disease_id
HAVING COUNT(symptom_id) > 1

Edit: to reply to the edited question

SELECT disease_id, COUNT(DISTINCT symptom_id)
FROM analyze
WHERE symptom_id IN ($symptom1, $symptom2, $symptom3)
GROUP BY disease_id
ORDER BY COUNT(DISTINCT symptom_id) DESC

Of course you'll have to replace $symptomX by their respective ID's.

This query lists the diseases which match at least one symptom - the diseases which match the most symptoms are on top.

If you added an unique constraint on symptom_id and disease_id in analyze, you could lose the DISTINCT:

SELECT disease_id, COUNT(symptom_id)
FROM analyze
WHERE symptom_id IN ($symptom1, $symptom2, $symptom3)
GROUP BY disease_id
ORDER BY COUNT(symptom_id) DESC

Answer 2

select d.id from disease d inner join analyze a
    on d.id = a.disease_id
    group by d.id having count(a.disease_id) > 1

Answer 3

select disease_id, count(*)
from analyze
where symptom_id in ($symptom1, $symptom2, $symptom3)
group by disease_id 
order by 2 descending;

will return the matching disease ids in descending order of matching symptoms.