How to select the records that have relation with all the values of a list?

Use a subquery in the WHERE clause that counts if the person has the required qualifications.

SELECT cp_pessoa.id, cp_pessoa.nome
FROM cp_pessoa
WHERE 
      -- Se a pessoa possuir ao menos as habilitações 71 e 695
      (SELECT COUNT(DISTINCT cp_habilidade_freelancer.id_habilidade)
       FROM cp_habilidade_freelancer
       WHERE cp_habilidade_freelancer.id_freelancer = cp_pessoa.id
         AND (cp_habilidade_freelancer.id_habilidade = 71 
           OR cp_habilidade_freelancer.id_habilidade = 695)
       ) = 2
LIMIT 0, 10

Taking advantage of the @ user4919 example: link

I can not be sure if this is correct because I can not test.

SELECT DISTINCT cp_pessoa.id, cp_pessoa.nome
   FROM cp_pessoa
LEFT JOIN cp_habilidade_freelancer ON (cp_habilidade_freelancer.id_freelancer = cp_pessoa.id)
LEFT JOIN cp_habilidade ON (cp_habilidade.id = cp_habilidade_freelancer.id_habilidade)
   WHERE (SELECT COUNT(*) FROM cp_habilidade_freelancer WHERE (id_habilidade = 71 OR id_habilidade = 695) AND cp_habilidade_freelancer.id_freelancer = cp_pessoa.id) = 2
LIMIT 0, 10

Use IN () in this way several values can be passed by comma.

    SELECT DISTINCT cp_pessoa.id, cp_pessoa.nome
   FROM cp_pessoa
LEFT JOIN cp_habilidade_freelancer ON (cp_habilidade_freelancer.id_freelancer = cp_pessoa.id)
LEFT JOIN cp_habilidade ON (cp_habilidade.id = cp_habilidade_freelancer.id_habilidade)
   WHERE cp_habilidade.id IN(71,695)
LIMIT 0, 10

Well, since you have a n-m (many-to-many) table, the difference is that your WHERE field will have to be filtered by a subquery or a complex JOIN. JOINs tend to be more performative than subqueries, but it's a matter of testing.

The simplest way, which can perform equal to or better than subquery / joins, especially for tables with many data, is to break your query in two. At first you access the cp_habilidade_freelancer table and get all id_freelancer that satisfy your query. Then, with these IDs, you can instead use IN() in the main query.

The way to do this in a single step is to take advantage of the query in the simple way and put it inside the main query. But be careful when your table is large and does not fit in memory.

I believe that using inner join and group by you resolve. Follow:

SELECT cp.id, cp.nome
FROM cp_pessoa as cp
inner JOIN  cp_habilidade_freelancer  as chf ON ( chf.id_freelancer=cp.id)
left   JOIN cp_habilidade ch ON (ch.id = chf.id_habilidade) 
and  (chf.id_habilidade =2 and  chf.id_habilidade =1)
group by (nome)