Help with (somewhat simple) MySQL JOIN

Question

Consider I have three tables:

• Movies (movie 1, movie 2, etc)
• Categories (action, suspense, etc)
• Movies_Categories (movie 1 -> action, movie 1 -> suspense, movie 2 -> suspense, etc)

How could I select only the movies that belong or don't belong to a specific category using only 1 query?

Thanks!

Answer 1

Belongs:

    SELECT m.*
      FROM movies m
INNER JOIN movies_categories mc
        ON m.id = mc.movie_id
INNER JOIN categories c
        ON c.id = mc.category_id
       AND c.name = 'action';

Doesn't belong:

         SELECT m.*
           FROM movies m
LEFT OUTER JOIN (SELECT mc.movie_id
                   FROM movies_categories mc
             INNER JOIN categories c
                     ON c.id = mc.category_id
                    AND c.name = 'action') mcx
             ON m.id = mcx.movie_id
          WHERE mcx.movie_id IS NULL

Answer 2

    SELECT m.*,
           IF(mc.movie_id IS NULL, 'doesnt belong', 'belongs')
      FROM Movies m
 LEFT JOIN Movies_Categories mc ON mc.movie_id = m.id
                               AND mc.category_id = (SELECT id
                                                       FROM categories
                                                      WHERE name = 'action')