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