MySQL: comparing multiple rows from a joint table to the primary table using NOT IN?
SELECT SQL_CALC_FOUND_ROWS e.*
FROM exercises e
LEFT JOIN exercise_targetedmuscles em ON em.exerciseID = e.exerciseID
WHERE (em.targetedMuscleID NOT IN(15,16,17,14,3,12,9,8,7,18,4,2) AND em.isPrimary = 1)
GROUP BY e.exerciseID
ORDER BY e.name ASC
开发者_Python百科I want to make sure that none of the targetted muscles of the exercise (of which there may be many) are in the list "(15,16,17,14,3,12,9,8,7,18,4,2)", however this only checks the first one it finds. How can I check all of the matching rows from exercise_targetedmuscles instead of just the first?
Thanks!
Try this:
SELECT SQL_CALC_FOUND_ROWS e.*
FROM exercises e
WHERE NOT EXISTS
(
SELECT 1
FROM exercise_targetedmuscles em
WHERE em.exerciseID = e.exerciseID
AND em.targetedMuscleID IN(15,16,17,14,3,12,9,8,7,18,4,2)
AND em.isPrimary = 1
)
GROUP BY e.exerciseID
ORDER BY e.name ASC
精彩评论