[development] SQL Query Doubt

[David Metzler]

Or....

Select * form employees e where 
  exists (select 1 from skills s where s.employee_id=e.employee_id and s.skill_id = 'java')
AND exists(select 1 from skills s where s.employee_id=e.employee_id and s.skill_id='C++')

I have heard that mysql will perform better with Ivan's query, but I haven't verified that this is the case. 

Some DB's preform bettter with this query than the join, some perform worse. 
On Apr 16, 2011, at 5:28 AM, Ivan Sergio Borgonovo wrote:

> On Sat, 16 Apr 2011 04:24:35 -0700
> Jason Flatt <drupal at oadaeh.net> wrote:
> 
> 
>> SELECT EmployeeID FROM {tablename} WHERE Skill = 'PHP' AND Skill =
>> 'Java';
> 
> So 'Java' = 'PHP', interesting.
> 
> create table test.employee (eid int, skill varchar (15), primary key
> (eid, skill));
> 
> select * from test.employee;
> 1;"PHP"
> 1;"SQL"
> 1;"Java"
> 1;"VoIP"
> 2;"Shell Scripting"
> 2;"PHP"
> 2;"Java"
> 3;"C++"
> 3;"PHP"
> 3;"Pearl"
> 
> data sample is a bit different to check employee 3 doesn't get
> selected.
> 
> select t1.eid from test.employee t1
>  join test.employee t2 on t1.eid=t2.eid
>  where t1.skill='Java' and t2.skill='PHP'
>  group by t1.eid
> ;
> 1
> 2
> 
> 
> 
> 
> 
> 
> 
> -- 
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>