Solved

SQL Minus Query

Posted on 2004-09-20
10
11,005 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
  • 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Another multiple criteria puzzle 4 19
How to make an ADE file by code? 11 42
Best Approach MS Access 2013 3 23
Query 14 22
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now