How to grab related rows between two tables?-CodePudding

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             |

CodePudding user response：

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

CodePudding user response：

You can do with an outer join or with a not exists, see if the following is what you need:

select * 
from exercises e
where not exists (
  select * from exercises x 
    where exists (
      select * from fitnessRecords fr
      where fr.name = 'john smith' and fr.motionName = x.motionName
    ) and x.primaryMuscleGroup = e.primaryMuscleGroup
)