Coder Coder -4 years ago 45
SQL Question

sql how i select actors who also producers

everyone.
I am new in sql and just wonder how to select all id_peoples from table who have both professions with one select.

my table looks like this

CREATE TABLE `films_peoples_prof` (
`id_film` int(11) NOT NULL,
`id_people` int(11) NOT NULL,
`id_profession` int(11) NOT NULL,
PRIMARY KEY (`id_film`,`id_people`,`id_profession`),
KEY `id_people` (`id_people`),
KEY `id_profession` (`id_profession`),
CONSTRAINT `films_peoples_prof_ibfk_1` FOREIGN KEY (`id_film`) REFERENCES `films` (`id`) ON DELETE CASCADE,
CONSTRAINT `films_peoples_prof_ibfk_2` FOREIGN KEY (`id_people`) REFERENCES `peoples` (`id`) ON DELETE CASCADE,
CONSTRAINT `films_peoples_prof_ibfk_3` FOREIGN KEY (`id_profession`) REFERENCES `professions` (`id`) ON DELETE CASCADE
)


+---------+-----------+---------------+
| id_film | id_people | id_profession |
+---------+-----------+---------------+
| 4 | 1 | 1 |
| 4 | 1 | 2 |
| 5 | 1 | 1 |
| 7 | 1 | 1 |
| 4 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 1 |
| 6 | 3 | 1 |
| 4 | 4 | 1 |
| 8 | 5 | 1 |
| 9 | 5 | 1 |
| 4 | 6 | 1 |
| 4 | 6 | 2 |
| 9 | 7 | 2 |
| 4 | 10 | 1 |
+---------+-----------+---------------+


id = 1 -actor, id = 2 - producer
in this case result should be 1 , 6

Answer Source

You need to use group by like

select id_people 
from films_peoples_prof
where id_profession in (1,2)
group by id_people 
having count(distinct id_profession ) = 2;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download