Home > IT Answers > Database > Query
catalina3x
10 pts.
 Query
Database, Query
I have a training database; one table lists employees by titles and training required; the other lists employees by name and training completed. I would like to do a query finding out which training that is required but the employee had not received, how would I create that query?

Software/Hardware used:
ASKED: February 26, 2013  1:44 PM
UPDATED: February 26, 2013  2:07 PM
RELATED QUESTIONS
Answer Wiki:
table employees is the employee table with required training.  Has columns empname and reqtrainingcourses. table donetraining has two columns empname and donecourses. Option 1 select empname, reqtrainingcourses from employees MINUS select empname, donecourses from donetraining; Option 2 select empname, reqtrainingcourses from employees where (empname, reqtrainingcourses) not in (select empname, donecourses from donetraining);
Last Wiki Answer Submitted:  February 28, 2013  8:02 pm  by  TomBubb   65 pts.
All Answer Wiki Contributors:  TomBubb   65 pts. , Michael Tidmarsh   14,000 pts. , catalina3x   10 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Would you tell us what software you are working in.
Could you give us a sample of the data in both tables.

philpl1jb
 44,630 pts.
 

Also, let us know what you have tried…

carlosdl
 63,580 pts.
 

If your database is the way you described it (Title and RequiredTraining in one table; EmployeeName and CompletedTraining in the other table), then there is no way to create the query. The tables need to have something in common. It’s almost certain that you need to tell us more about the two tables or about some other table that you haven’t mentioned yet. There needs to be at least one table that links Title and EmployeeName together in some way. — Tom

TomLiotta
 110,115 pts.
 

Is this resolved?

philpl1jb
 44,630 pts.
 

so, you might try something like this … 

The real problem with this design is that
empname must be typed exactly the same in both tables
and reqtrainingcourses must be exactly the same as donetraining

Usually these matches are managed by using id numbers instead of values
and having two other files.  
One table with empid and employee name 
the other with courseId and courseName.
But given these issues the following might work...

select e.empname, e.reqtrainingcourses d.empname
from employees e left join donetraining d
on e.empname = d.empname and 
e.reqtrainingcourses = d.donecourses
where d.empname is null;
philpl1jb
 44,630 pts.
 

but your minus should work .. if there are exact matches on  both field values.. if you are still having issues please provide samples of your data. 

select empname, reqtrainingcourses from employees 
MINUS 
select empname, donecourses from donetraining;
philpl1jb
 44,630 pts.