Выборка поля1 в зависимости от значений поля2 в разных строках

[WBS]

Сразу приведу пример. Есть таблица (actor_film), связывающая актеров (id_actor) и фильмы (id_film). Очевидно, один актер может сниматься во многих фильмах, а в одном фильме множество актеров. Требуется найти таких актеров, которые одновременно снимались в фильмах №1 и №2.

Я представляю 2 способа как это сделать:

1. Соединение таблицы самой с собой
SELECT R1.id_actor FROM actor_film R1, actor_film R2
WHERE R1.id_actor=R2.id_actor AND R1.id_film=1 AND R2.id_film=2

2. Пересечение запросов
SELECT id_actor FROM actor_film WHERE id_film=1
INTERSECT
SELECT id_actor FROM actor_film WHERE id_film=2

Оба способа недостаточно гибкие на мой взгляд, и при усложнении критерия выбора актеров (например, актер снимался в фильмах "(1 и 2) или (3 и 4)") использовать их не очень удобно.

Кто-нибудь может посоветовать более быстрое и красивое решение?

 

[akd]

ну, например, группировка и хевинг.

create table tt (actor int, film int);
Query OK, 0 rows affected (0.13 sec)

mysql> select * from tt;
Empty set (0.00 sec)

mysql> insert into tt (actor, film) values (1,1), (1,2), (1,3), (2,1), (2,2);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into tt (actor, film) values (3,1), (3,3), (4,1), (5,2), (6,1);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from tt;
+-------+------+
| actor | film |
+-------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 3 |
| 4 | 1 |
| 5 | 2 |
| 6 | 1 |
+-------+------+
10 rows in set (0.00 sec)


mysql> select * from tt where film in(1,2) group by actor having count(*) = 2;
+-------+------+
| actor | film |
+-------+------+
| 1 | 1 |
| 2 | 1 |
+-------+------+
2 rows in set (0.00 sec)


mysql> select * from tt where film in(1,3) group by actor having count(*) = 2;
+-------+------+
| actor | film |
+-------+------+
| 1 | 1 |
| 3 | 1 |
+-------+------+
2 rows in set (0.00 sec)

mysql>

 

[WBS]

ну, например, группировка и хевинг

Вариант неплохой, но тоже не очень-то гибкий. Составить запрос с более сложным критерием выбора тут тоже проблематично.

 

[akd]

ээ, жить тоже не очень просто и местами проблематично