I'm building a symptom checker. As the user picks symptoms, we suggest other symptoms from ailments that have the user selected symptoms in common until we can narrow down to a specific ailment. I have this table.
ailment_symptoms
+----+----------+-----------+
|id |ailment_id|symptom_id |
+----+----------+-----------+
|1 | 1 | 1 |
|2 | 1 | 2 |
|3 | 2 | 1 |
|4 | 2 | 3 |
|5 | 3 | 3 |
|6 | 3 | 2 |
|7 | 4 | 1 |
|8 | 4 | 2 |
+----+----------+-----------+
If I want to select ailment_ids of entries that have symptom_id 1 and 2, I use this self join query.
SELECT t1.ailment_id
FROM ailment_symptoms t1
JOIN ailment_symptoms t2 ON t1.ailment_id = t2.ailment_id
WHERE t1.symptom_id = '1'
AND t2.symptom_id = '2'
which will return
+----------+
|ailment_id|
+----------+
| 1 |
| 4 |
+----------+
How do I do it when there are more than two symptom_ids. I want to write a php code that will build for as many symptoms as the users enters. code should look like:
$user_symptoms = array($symptom_id_1, $symptom_id_2, $symptom_id_3); //as many symptom as the user picks
$query = "SELECT t1.ailment_id FROM ailment_symptoms t1";
foreach($user_symptoms AS $symptoms){
//here is where we construct the multiple self join and append it to $query
//please replace this comment with appropriate code
}
$query .= "WHERE ";
//loop through $user_symptoms and append t1.symptom_id = '$user_symptom[0]' AND '....'
Please help me replace the comments with the appropriate code.
You can also do this with aggregation. It might be easier for you to construct the queries like this, because it is easier to handle the additional attributes.
SELECT s.ailment_id
FROM ailment_symptoms s
WHERE s.symptom_id in (1, 2)
GROUP BY s.ailment_id
HAVING COUNT(DISTINCT s.symptom_id) = 2;
You just have to be sure that the "2" matches the number of elements in the list in the where
clause. Then it will generalize to any number of symptoms.