Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11019
  • Last Modified:

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#
             
0
d_vonfeldt
Asked:
d_vonfeldt
  • 4
  • 4
  • 2
1 Solution
 
GRayLCommented:
Select * from employee, employee_skill inner join employee.empid = employee_skill.empid where employee_skill <> 2;
0
 
shanesuebsahakarnCommented:
SELECT * FROM employee WHERE empid NOT IN (SELECT empid FROM employee_skill As A WHERE A.skill_id=2 AND A.empid=employee.empid)
0
 
d_vonfeldtAuthor Commented:
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
d_vonfeldtAuthor Commented:
Thanks,

That was quick and easy.
0
 
GRayLCommented:
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;
0
 
GRayLCommented:
Still got it wrong:

Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
0
 
shanesuebsahakarnCommented:
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.
0
 
d_vonfeldtAuthor Commented:
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;
0
 
d_vonfeldtAuthor Commented:
perfect explanation shanesuebsahkarn,  that is what was giving me the troubles.

0
 
GRayLCommented:
Nicely done Shane.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now