Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Select values that have at least ID from array

Select values that have at least ID from array

Author: rootsql Date: 23-11-2011, 23:59

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   |
+---------+----------+
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.