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 <[email protected]> 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 >
