Tuesday, 2 June 2015

Mysql: Select values that have all ID from array

We have a table film_actor that represents many to many relationship between films and actors.
CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the only actors from a given array { 5, 8, 10 } were starring:
SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0 AND
  COUNT(IF(actor_id <> 5 AND actor_id <> 8 AND actor_id <> 10, 1, NULL)) = 0;
+---------+
| film_id |
+---------+
|       3 |
+---------+

The next query will return the same films:
SELECT film_id, GROUP_CONCAT(actor_id ORDER BY actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  actors = '5,8,10';
+---------+--------+
| film_id | actors |
+---------+--------+
|       3 | 5,8,10 |
+---------+--------+

0 comments:

Post a Comment