Hi All Lets say I have one table in below format. EmployeeID.............. Skill ------------------------------------------------------ 1...............................PHP 1...............................SQL 1...............................Java 1................................VoIP 2................................Shell Scripting 2................................PHP 2................................Java 3............................... C++ 3................................C 3.................................Pearl I wan to do a SQL query for employess who MUST have skill PHP and Java. As per above table, the query result should show Employee 1 and 2. Now what should be the corresponding SQL query for it. Regards Austin
On Sat, 16 Apr 2011 16:09:59 +0530 Austin Einter <austin.einter@gmail.com> wrote:
Hi All
Lets say I have one table in below format.
EmployeeID.............. Skill ------------------------------------------------------ 1...............................PHP 1...............................SQL 1...............................Java 1................................VoIP
2................................Shell Scripting 2................................PHP 2................................Java
3............................... C++ 3................................C 3.................................Pearl
I wan to do a SQL query for employess who MUST have skill PHP and Java. As per above table, the query result should show Employee 1 and 2.
Now what should be the corresponding SQL query for it.
SELECT EmployeeID FROM {tablename} WHERE Skill = 'PHP' AND Skill = 'Java'; This might help a bit: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html -- Jason Flatt Custom Website development solutions: http://www.oadaeh.net/ Father of seven: http://www.flattfamily.com/ (Joseph, 18; Cramer, 16; Travis, 14; Angela; Harry, 10; William, 5; and Dolly, 2) Linux user: http://www.ubuntu.org/ Android user: http://www.android.com/ Drupal fanatic: http://drupal.org/
Hi Thanks for reply. I have tried SELECT EmployeeID FROM {tablename} WHERE Skill = 'PHP' AND Skill = 'Java'; . It does not work. Probably in SQL in same row of table both values are not matching. Regards, Austin On Sat, Apr 16, 2011 at 4:54 PM, Jason Flatt <drupal@oadaeh.net> wrote:
On Sat, 16 Apr 2011 16:09:59 +0530 Austin Einter <austin.einter@gmail.com> wrote:
Hi All
Lets say I have one table in below format.
EmployeeID.............. Skill ------------------------------------------------------ 1...............................PHP 1...............................SQL 1...............................Java 1................................VoIP
2................................Shell Scripting 2................................PHP 2................................Java
3............................... C++ 3................................C 3.................................Pearl
I wan to do a SQL query for employess who MUST have skill PHP and Java. As per above table, the query result should show Employee 1 and 2.
Now what should be the corresponding SQL query for it.
SELECT EmployeeID FROM {tablename} WHERE Skill = 'PHP' AND Skill = 'Java';
This might help a bit: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html
-- Jason Flatt Custom Website development solutions: http://www.oadaeh.net/ Father of seven: http://www.flattfamily.com/ (Joseph, 18; Cramer, 16; Travis, 14; Angela; Harry, 10; William, 5; and Dolly, 2) Linux user: http://www.ubuntu.org/ Android user: http://www.android.com/ Drupal fanatic: http://drupal.org/
On Sat, 16 Apr 2011 04:24:35 -0700 Jason Flatt <drupal@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
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@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
On 04/16/2011 05:28 AM, Ivan Sergio Borgonovo wrote:
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 ;
You can do a little better than that, I think, but a similar idea: select t1.ed from employee t1 inner join employee t2 on t1.eid = t2.eid and t2.skill = 'Java' where t1.skill = 'PHP'; No need for the group by.
1 2
participants (5)
-
Austin Einter -
David Metzler -
Ivan Sergio Borgonovo -
Jason Flatt -
Rob Thorne