Solved

SQL Minus Query

Posted on 2004-09-20
10
11,009 Views
Last Modified: 2012-08-13
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
Comment
Question by:d_vonfeldt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 12106204
Select * from employee, employee_skill inner join employee.empid = employee_skill.empid where employee_skill <> 2;
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 200 total points
ID: 12106282
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
 

Author Comment

by:d_vonfeldt
ID: 12106284
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:d_vonfeldt
ID: 12106353
Thanks,

That was quick and easy.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12106412
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
 
LVL 44

Expert Comment

by:GRayL
ID: 12106424
Still got it wrong:

Select * from employee inner join employee.empid = employee_skill.empid where employee_skill.skill_id <> 2;
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12106476
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
 

Author Comment

by:d_vonfeldt
ID: 12106489
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
 

Author Comment

by:d_vonfeldt
ID: 12106513
perfect explanation shanesuebsahkarn,  that is what was giving me the troubles.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 12106666
Nicely done Shane.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question