MySQL选择与相关表中的多行匹配的行 [英] MySQL Select rows that match multiple rows in related table

问题描述

以下表格要大得多，但为了便于回答已将其缩小了尺寸

The following tables are much larger, but have been downsized for ease of the question

表1 -exercise_rolladex

Exercise_ID | Exercise_Name
---------------------------
1            Pushups
2            Turkish Get Ups
3            Squats
4            Ice Skater

表2-运动飞机

Exercise_Plane_ID | Exercise_Plane
----------------------------------
1                  Sagittal
2                  Frontal
3                  Transverse

表3-Exercise_has_planes

Exercise_ID | Exercise_Plane_ID
-------------------------------
1             1
2             1
2             2
2             3
3             1
4             2
4             3

我的问题是:如何构造一个查询，可以在其中找到每个具有Exercise_Plane_ID = 1和Exercise_Plane_ID = 2的运动的Exercise_ID.换句话说，找到既具有矢状运动面又具有额骨运动面的运动.

My question is: How can I structure a Query where I can find the Exercise_ID of each exercise which has Exercise_Plane_ID=1 AND Exercise_Plane_ID=2. In other words, find the exercises that have both Sagittal AND Frontal planes of motion.

正确的查询

   SELECT e.Exercise_Name, p.Exercise_Plane 
   FROM exercise_rolladex e
   INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID
   INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID
   WHERE p.Exercise_Plane_ID IN(2,1)
   GROUP BY e.Exercise_ID
   HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

更新后续问题 那我怎么包括一个排除项呢?例如，找到使用plane_id 2和3的练习，但排除使用plane_id 1的练习(正确的结果是"Ice Skater")

UPDATE FOLLOW UP QUESTION How then would I include an exclusion? for example, find the exercises with plane_id 2 and 3, but exclude exercises with plane_id 1 (The correct result being "Ice Skater")

我继续回答了自己的问题:

I went ahead and answered my own question:

   SELECT e.Exercise_Name, p.Exercise_Plane 
   FROM exercise_rolladex e
   INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID
   INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID
   WHERE p.Exercise_Plane_ID IN(2,3)
       AND e.Exercise_ID NOT IN
       (SELECT Exercise_ID FROM exercise_has_planes WHERE Exercise_Plane_ID='1')
   GROUP BY e.Exercise_ID
   HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

多谢布朗斯通茨先生回答的另一个问题. SQL查询可基于一个查询排除项目值

Thanks to Mr. Brownstones answer from a different question. SQL query to exclude items on the basis of one value

推荐答案

您可以执行以下操作，这将使用给定的输入ID检查计划ID，如果计数返回多个，则将每个练习组中的计数过滤掉那就意味着运动有平面，有子句可以满足两个平面都处于运动状态的情况

You can do something like this,this will check the plan id with your given input ids and filter out there count in each exercise group if count returns more than one then it means exercise has planes,having clause will fulfill the scenario of having both planes in exercise

SELECT e.Exercise_Name, 
p.Exercise_Plane 
FROM exercise_rolladex e
INNER JOIN exercise_has_planes h ON h.Exercise_ID=e.Exercise_ID
INNER JOIN exercise_planes p ON p.Exercise_Plane_ID=h.Exercise_Plane_ID
WHERE p.Exercise_Plane_ID IN(2,1)
GROUP BY e.Exercise_ID
HAVING COUNT(DISTINCT h.Exercise_Plane_ID ) >= 2

这篇关于MySQL选择与相关表中的多行匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！