MySQL Group_Concat Not In

I haven't been able to find a similar question with an answer I can use, hopefully I am not overlooking another one.

I am wanting to build a query that returns items where a value is NOT IN a group_concat. Let me show with an example:

I have a table of exercises:

id  |  ExerciseName
---------------------
1   |  Squat
2   |  Deadlift
3   |  Bench Press
4   |  Deadlift Hex Bar

Then I have a separate table that shows what equipment each of these uses:

id  | ExerciseId  |  EquipmentId
---------------------------------
1   | 1           | 1
2   | 1           | 20
3   | 2           | 1
4   | 3           | 47

Then I have a separate table that shows what the name of each equipment:

id  | name_en
-------------
1   | Barbell
2   | Squat Rack
3   | Dumbbell

Now, I have a query I am running that can select exercises, and I want to query exercises WHERE the equipment is available for a user. So let's say a User does not have Equipment with Id = 1. Then I would want to return Exercise 3 only. Currently, in my query, I am getting Exercise 1 and 3, and it is doing this because Exercise 1 does have Equipment Id = 1, but it also has another that is not equal to Equipment Id = 1 (as it is Id = 20).

SELECT
    we.id ExerciseId,
    we.name_en ExerciseName
    GROUP_CONCAT(DISTINCT weeq.name_en ORDER BY weeq.name_en SEPARATOR ', ') Equipment
    FROM workouts_exercise we
JOIN workouts_exercise_equipment weeq ON weeq.exercise_id = we.id
JOIN workouts_equipment weq ON weq.id = weeq.equipment_id
WHERE weq.id NOT IN ("1")
GROUP BY we.id

The results of this, as mentioned previously are like this:

ExerciseId  | ExerciseName   | Equipment
------------------------------------------
1           | Squat          | 20
3           | Bench Press    | 47

when in reality, all I want is this:

ExerciseId  | ExerciseName   | Equipment
------------------------------------------
3           | Bench Press    | 47

Solution

One approach would be to use an anti-join to filter off exercises which use one or more of the equipment which you want to exclude:

SELECT
    we1.ExerciseId,
    ex.ExerciseName,
    we1.EquipmentId AS Equipment
FROM workouts_equipment we1
LEFT JOIN workouts_equipment we2
    ON we1.ExerciseId = we2.ExerciseId AND
       we2.EquipmentId IN (1)
INNER JOIN workouts_exercise ex
    ON we1.ExerciseId = ex.id
WHERE
    we2.ExerciseId IS NULL;

MySQL Group_Concat Not In

Demo