Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Select values that have all ID from array

Select values that have all ID from array

Author: rootsql Date: 24-11-2011, 00:00

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 |
+---------+--------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.