Query

10 pts.
Tags:
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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);

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Would you tell us what software you are working in. Could you give us a sample of the data in both tables.
    49,850 pointsBadges:
    report
  • carlosdl
    Also, let us know what you have tried...
    69,175 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • philpl1jb
    Is this resolved?
    49,850 pointsBadges:
    report
  • philpl1jb
    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;
    
    49,850 pointsBadges:
    report
  • philpl1jb
    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;
    
    49,850 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following