How to select records that have a relationship with all values in a list?

Question

I have the following sql:

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 = 71 OR cp_habilidade.id = 695
LIMIT 0, 10

I only want people (cp_person) who have all the skills (71, 695).

It may seem simple, but I’m struggling.

Examples:

If I use OR the following persons with the following abilities (1,2,71) are returned (persons without the 695 skill). If I use AND the following persons with the following abilities (71, 695) are not returned

example: sqlfiddle

Answer 1

Use a subquery in the clause WHERE whether the person possesses the requisite 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 the example of @user4919: http://sqlfiddle.com/#! 2/dd233/33/0

Answer 2

I can’t be sure if this is correct because I have no way to 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

Answer 3

I believe that using Internet and group by you solve.Follows:

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)

Answer 4

Use the IN() thus various 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

Answer 5

Well, as it has an n-m table (many to many) in general the difference is that its field WHERE will have to be filtered by a subquery or a complex JOIN. Joins tend to be more performative than Ubqueries, but it’s a matter of testing.

The simplest way, which can perform equally or even better than subquery/joins, especially for tables with Too Much Data, is for you to break your query in two. At first you access the table cp_habilidade_freelancer and get all the id_freelancer that satisfy your query. Then, with these Ids, you can use the 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 inside the main query. But be careful when your table is large and does not fit in memory.