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