d_vonfeldt
asked on
SQL Minus Query
I am looking for a solution that would be similar to Oracle's minus query. I have two tables, an employee table and an employee_skill table. I need a query that will pull out all employees that do not have a certain skill.
example: Select * from employee, employee_skill
where employee.empid = employee_skill.empid
MINUS
Select * from employee, employee_skill
where employee.empid = employee_skill.empid
and skill_id = 2 //or whatever skill#
example: Select * from employee, employee_skill
where employee.empid = employee_skill.empid
MINUS
Select * from employee, employee_skill
where employee.empid = employee_skill.empid
and skill_id = 2 //or whatever skill#
Select * from employee, employee_skill inner join employee.empid = employee_skill.empid where employee_skill <> 2;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Select * from employee, employee_skill inner join employee.empid = employee_skill.empid where employee_skill <> 2;
that line of code gave me a syntax error in FROM clause message.
Is that line equivalent to: Select * from employee, employee_skill
where employee.empid = employee_skill.empid
and skill_id <> 2.
I am not too familiar with inner joins?
that line of code gave me a syntax error in FROM clause message.
Is that line equivalent to: Select * from employee, employee_skill
where employee.empid = employee_skill.empid
and skill_id <> 2.
I am not too familiar with inner joins?
ASKER
Thanks,
That was quick and easy.
That was quick and easy.
Sorry, I got it wrong. For the record, I think it should be:
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill <> 2;
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill <> 2;
Still got it wrong:
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
GRayL, you have not specified the join table in your query. It should read (modifications in caps):
Select EMPLOYEE.* from employee inner join EMPLOYEE_SKILL ON employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
However, this only returns records where the skill_id is not 2 - if an employee has skill 2, it simply doesn't include that record in the list, but includes all of his other skill records. It doesn't return records for employees who don't have skill 2 at all.
Select EMPLOYEE.* from employee inner join EMPLOYEE_SKILL ON employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
However, this only returns records where the skill_id is not 2 - if an employee has skill 2, it simply doesn't include that record in the list, but includes all of his other skill records. It doesn't return records for employees who don't have skill 2 at all.
ASKER
I am still getting the same error when using
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
ASKER
perfect explanation shanesuebsahkarn, that is what was giving me the troubles.
Nicely done Shane.