Tuesday 2 June 2015

Mysql: Select values that have at least 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 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;
+---------+
| film_id |
+---------+
|       1 |
|       3 |
+---------+

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

0 comments:

Post a Comment