如何抓取两个表之间的相关行？

Question

我的桌子：

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             |

查询应该为一个人返回他们没有做过的肌肉群的所有练习。例如，如果我们运行对客户端"John“的查询，我们应该返回：

| primaryMuscleGroup | motionName           |
| Legs               | Squat                |
| Legs               | Lunge                |
| Back               | Deadlift             |

如果我们运行对客户端"Sally“的查询，我们应该返回：

| primaryMuscleGroup | motionName           |
| ------------------ | -------------- ------|
| Chest              | Dips                 |
| Chest              | Chest Press          |
| Chest              | Push Up              |
| Chest              | Flye                 |
| Back               | Deadlift             |

Answer 1

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

Answer 2

您可以使用外部联接或not exists来完成，查看以下内容是否满足您的需要：

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
)