MySQL Select rows that match multiple rows in related table

Question

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

Table 1 - exercise_rolladex

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

Table 2 - exercise_planes

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

Table 3 - exercise_has_planes

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

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.

The Correct Query

   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

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

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

+1 excellent post. If I wanted to hit the db with a series of 'where p.Exercise_ID In (2,3)' inquiries ... would that be repetitive calls or is there such a mysql query that could bring back an array of arrays given an input like 'where p.Exercise_ID IN (array(2,3),array(3,4),array(5,6)) ? Seems like a php loop should be hitting the db, but I had to ask. — Ricalsin
– Ricalsin, Commented Oct 17, 2016 at 2:17

M Khalid Junaid · Accepted Answer · 2014-05-07 05:46:45Z

6

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

Demo

edited May 7, 2014 at 5:46

answered May 7, 2014 at 5:31

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The4thIceman Over a year ago

@M Khalid Junaid - Thanks for the answer. works well. I updated my post with a follow up question involving exclusion if you want to take a crack at it.

M Khalid Junaid Over a year ago

@The4thIceman you are most welcome and i gave you an upvote for the effort of your own follow answer keep it up

Collectives™ on Stack Overflow

MySQL Select rows that match multiple rows in related table

1 Answer 1

Demo

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related