Matching Exactly all values in IN clause-CodePudding

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.

CodePudding user response：

Skip the WHERE clause. Use HAVING to make sure exactly a and b are there.

SELECT workouts.id
FROM workouts
JOIN target_muscles ON workouts.id = target_muscles.workout_id
GROUP BY workouts.id
HAVING COUNT(DISTINCT target_muscles.muscle_key) =
       COUNT(DISTINCT CASE WHEN target_muscles.muscle_key IN (a,b)
                           THEN target_muscles.muscle_key END)
   AND COUNT(DISTINCT target_muscles.muscle_key) = 2

Can also be done as:

SELECT workouts.id
FROM workouts
JOIN target_muscles ON workouts.id = target_muscles.workout_id
GROUP BY workouts.id
HAVING MIN(target_muscles.muscle_key) = 'a'
   AND MAX(target_muscles.muscle_key) = 'b'
   AND COUNT(DISTINCT target_muscles.muscle_key) = 2

Or, perhaps less performant:

SELECT workouts.id
FROM workouts
JOIN (SELECT workout_id FROM target_muscles WHERE muscle_key = 'a'
      INTERSECT
      SELECT workout_id FROM target_muscles WHERE muscle_key = 'b'
      EXCEPT
      SELECT workout_id FROM target_muscles WHERE muscle_key NOT IN ('a', 'b')) dt
  ON workouts.id = dt.workout_id

CodePudding user response：

You can remove the filtering clause and use two conditions:

count of non-(a,b) muscle_keys = 0
distinct count of (a,b) muscle_keys = 2

SELECT w.id
FROM      workouts w
LEFT JOIN target_muscles ts ON w.id = ts.workout_id
GROUP BY w.id
HAVING COUNT(CASE WHEN ts.muscle_key NOT IN ('a', 'b') THEN w.id END) = 0
   AND COUNT(DISTINCT CASE WHEN ts.muscle_key IN ('a', 'b') THEN ts.muscle_key END) = 2

Check the demo here.

CodePudding user response：

This is an other way to do it using inner join

select distinct s.id
from (
  select w.id
  from workouts w
  inner join targets t on t.workout_id = w.id
  group by workout_id
  having count(1) = 2
) as s
inner join targets t on t.workout_id = s.id and t.muscle_key in ('a', 'b');

You can Try it from here : https://dbfiddle.uk/nPTQlhqT