Re: Help with select statement

[Chris Stubben]

Hi,

If the id and skill combination are unique (unique key (id, skill)), you can just count the number of rows using an aggregate function and add a HAVING clause...


create table tmp(id int, skill char(1));
insert into tmp values (1,'a'), (1,'b'), (1,'c'), (2,'a'), (2,'c'), (3,'b');

NOTE: (skill='a' or skill='b') is the same as skill in ('a', 'b')


select id from tmp where id=1 and skill in ('a', 'b') group by id having count(*)=2;
+------+
| id |
+------+
| 1 |
+------+

select id from tmp where id=2 and skill in ('a', 'b') group by id having count(*)=2;

Empty set (0.00 sec)

or search ALL rows

select id from tmp where skill in ('a', 'b') group by id having count(*)=2;


---
You can also use a subselect or self-join ...


select DISTINCT id from tmp where id in (select id from tmp where skill='a') and id in (select id from tmp where skill='b');
+------+
| id |
+------+
| 1 |
+------+


or self join (one join needed for each skill)

select t1.id, t1.skill, t2.skill from tmp t1, tmp t2
where t1.id=t2.id and t1.skill='a' and t2.skill= 'b';
+------+-------+-------+
| id | skill | skill |
+------+-------+-------+
| 1 | a | b |
+------+-------+-------+


Chris