I am searching for the solution for this problem for hours now with no luck. I have a Workouts table as below. Each item in the workout table can have multiple target muscles, which are listed in the Target Muscles table.
Workouts table:
id |
---|
1 |
2 |
Target Muscles table:
id | muscle_key | workout_id |
---|---|---|
1 | a | 1 |
2 | b | 1 |
3 | c | 1 |
4 | a | 2 |
5 | b | 2 |
I need to fetch all items in the workouts table which match EXACTLY ALL target muscles keys in the given set, not less and not more. For example, given the set of muscle keys:
(a,b)
The desired output would be:
id |
---|
2 |
The row for workout id = 1 should NOT be selected since it contains an extra muscle key (c).
I am using the following query:
SELECT id
FROM workouts
LEFT JOIN target_muscles ON workouts.id = target_muscles.workout_id
WHERE target_muscles.muscle_key IN (a,b)
GROUP BY workouts.id
HAVING COUNT(DISTINCT target_muscles.muscle_key) = 2
The above query is also returning the workout id = 1, instead of only 2. How can I achieve this?
Any help is appreciated.
3
Answers
Skip the
WHERE
clause. UseHAVING
to make sure exactly a and b are there.Can also be done as:
Or, perhaps less performant:
You can remove the filtering clause and use two conditions:
Check the demo here.
This is an other way to do it using
inner join
You can Try it from here : https://dbfiddle.uk/nPTQlhqT