CODEWITHSUNDEEP
mysql
NeoTechni
NeoTechni

Reputation: 179

Searching for groups of rows where a column contains ALL given values

I have a table symptom_ratings containing the columns id, user_id, review_id, symptom_id, rate, and strain_id.

Each review can have multiple entries in symptom_ratings, one per symptom.

I would like to do a search for every strain_id that has all of the symptom_id's the user searches for.

That is, given the columns:

review: 2, strain_id: 3, symptom_id: 43  
review: 2, strain_id: 3, symptom_id: 23  
review: 2, strain_id: 3, symptom_id: 12

review: 6, strain_id: 1, symptom_id: 3  
review: 6, strain_id: 2, symptom_id: 12

Searching for the symptom_id's 43 and 12 should only return results for strain_id 3.

I currently use the following WHERE condition: 

Strain.id IN (SELECT strain_id
    FROM symptom_ratings
    WHERE symptom_id
    IN ($symptoms))

where $symptoms is a comma-separated list of symptom_id values.

My problems is that this query currently performs an OR search (i.e. it finds strains that have any of the symptoms), where instead I'd prefer an AND search (i.e. finding strains that have all of the symptoms). How can I achieve that?

Upvotes: 1

Views: 56

Answers (1)

Ilmari Karonen
Ilmari Karonen

Reputation: 50378

One way to do this would be to group the rows by the strain ID, count the number of distinct matching symptoms in each group, and return only those rows where the count equals the total number of symptoms searched for:

SELECT
    strain_id,
    COUNT(DISTINCT symptom_id) AS matched_symptoms
FROM symptom_ratings
WHERE symptom_id IN (43, 12)
GROUP BY strain_id
HAVING matched_symptoms = 2

Here's a quick online demo.

One potentially useful feature of this method is that it's trivial to extend it to support both "all of these", "any of these" and "at least n of these" searches just by changing the condition in the HAVING clause. For the latter cases, you can also sort the results by the number of matching symptoms (e.g. with ORDER BY matched_symptoms DESC).

Upvotes: 1

Related Questions