Hello I’ve tried to solve this query myself and I am a bit lost. I was hoping to get some guidance. Here are my tables:
Table 1
excercises
| primaryMuscleGroup | motionName |
| ------------------ | -------------- ------|
| Chest | Dips |
| Chest | Chest Press |
| Chest | Push Up |
| Chest | Flye |
| Legs | Squat |
| Legs | Lunge |
| Back | Deadlift |
Table 2
fitnessRecords
| name | motionName |
| ------------------ | -------------- ------|
| John Smith | Dips |
| Sally | Squat |
| Wallace | Lunge |
| Christoph | Deadlift |
The query should return for a person all the exercises of a muscle group they have not done. For example if we run the query for the client "John Smith" we should return:
| primaryMuscleGroup | motionName |
| Legs | Squat |
| Legs | Lunge |
| Back | Deadlift |
if we run the query for the client "Sally" we should return:
| primaryMuscleGroup | motionName |
| ------------------ | -------------- ------|
| Chest | Dips |
| Chest | Chest Press |
| Chest | Push Up |
| Chest | Flye |
| Back | Deadlift |
Any help would be greatly appreciated :).
>Solution :
SELECT *
FROM excercises t1
WHERE NOT EXISTS ( SELECT NULL
FROM fitnessRecords t2
JOIN excercises t3 USING (motionName)
WHERE t2.name = 'given name'
AND t1.primaryMuscleGroup = t3.primaryMuscleGroup )
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=eb216b7579d5fcd0c0ab628717f3d676