Check if there are multiple values in a column

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_pessoa) who have all the skills (71, 695).

It may seem simple, but I'm struggling.

Examples:

If I use OR the following people with the following skills (1,2,71) are returned (people without the ability 695) If I use AND the following people with the following skills (71, 695) are not returned

Some remarks as to your own query: 1. Select DISTINCT is very often a sign for a bad query - you select duplicates and then you need remove them. Why do you outer join? You only want freelancers with those skills, so why include persons who are not freelancers, why include freelancers that have none of the desired skills? The where clause removes all these records again. And why do you even join cp_habilidade when cp_habilidade_freelancer.id_habilidade already contains the information you are looking for? — Thorsten Kettner
– Thorsten Kettner, Commented Feb 10, 2014 at 9:41

Thorsten Kettner · Accepted Answer · 2014-02-10 10:00:08Z

1

Is this homework? If so, the idea should suffice: You are looking for persons where the count of the associtated skills 71 and 695 is 2.

In case this is no homework, but a real task, then tell me so and I'll give you a complete SQL statement :-)

EDIT: The straight-forward way is to ask if both skills exist for a person:

select id, nome
from cp_pessoa p
where exists
(
  select *
  from cp_habilidade_freelancer hf
  where hf.id_freelancer = p.id 
  and hf.id_habilidade = 71
)
and exists
(
  select *
  from cp_habilidade_freelancer hf
  where hf.id_freelancer = p.id 
  and hf.id_habilidade = 695
);

The other way with querying cp_habilidade_freelancer just once:

select id, nome
from cp_pessoa p
where id in
(
  select id_freelancer
  from cp_habilidade_freelancer hf
  where id_habilidade in (71, 695)
  group by id_freelancer
  having count(distinct id_habilidade) = 2
);

You could change count(distinct id_habilidade) to count(*) in case it is guaranteed that there are no duplicate skills for an id_freelancer in cp_habilidade_freelancer.

edited Feb 10, 2014 at 10:00

answered Feb 10, 2014 at 9:18

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4919 Over a year ago

This is a real task :)

Thorsten Kettner Over a year ago

Okay. From your comment on the other answer I gather that you are not looking for "persons who have both skills 71 and 695", but for "persons who have both skills 71 and 695 and no other skill". Correct?

user4919 Over a year ago

No no, the person can have others skills, but 71 and 695 is mandatory

Thorsten Kettner Over a year ago

I have edited my answer, giving you two select statements you can use.

Collectives™ on Stack Overflow

Check if there are multiple values in a column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related