Need help with a complex SQL query

rrattie
rrattie used Ask the Experts™
on
Okay here is what I am trying to do.

I want to find all the employees who have NOT completed a certain training course.
I can easily find all those employees who have completed, but I cannot for the life of
me figure out how to find those who have not.

The tables and columns used are below.

The employee status must be active and completed is yes for those who have completed the course.
If the employee has not completed the course, they won't have any records for that class in the usertraining table.




Table: (column, column, etc..)

employees: (ID, last_name, first_name, dept, status)
courses: (id, course_name)
usertraining: (empID, courseID, completed)


This is what I have so far, that pulls the completed list.

SELECT     dbo.employees.ID, dbo.courses.id AS tc.id, dbo.employees.last_name, dbo.employees.first_name, dbo.employees.dept, dbo.employees.status,
                      dbo.courses.course_name, dbo.usertraining.empID, dbo.usertraining.courseID, dbo.usertraining.completed
FROM         dbo.employees INNER JOIN
                      dbo.usertraining ON dbo.employees.ID = dbo.usertraining.empID INNER JOIN
                      dbo.courses ON dbo.usertraining.CourseID = dbo.courses.id
WHERE     (dbo.employees.status = 'active') AND (dbo.usertraining.completed = 'yes')


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT     dbo.employees.ID, dbo.courses.id AS tc.id, dbo.employees.last_name, dbo.employees.first_name, dbo.employees.dept, dbo.employees.status,
                      dbo.courses.course_name, dbo.usertraining.empID, dbo.usertraining.courseID, dbo.usertraining.completed
FROM         dbo.employees
LEFT JOIN
                      dbo.usertraining ON dbo.employees.ID = dbo.usertraining.empID
INNER JOIN
                      dbo.courses ON dbo.usertraining.CourseID = dbo.courses.id
WHERE     (dbo.employees.status = 'active') AND (dbo.usertraining.completed = 'yes')
  AND  dbo.usertraining.empID IS NULL
oops, I meant to remove those two other conditions from the WHERE clause that would indicate the course is complete.
The point is, that "If the employee has not completed the course, they won't have any records for that class in the usertraining table", then find rows which do not have matches in that table, thus left-joining that table and WHERE dbo.usertraining.empID IS NULL.  But the first two conditions in the WHERE clause above might produce the wrong results.

Author

Commented:
I think that is the right direction, but I forgot to add to the WHERE clause before.

It should have been:


WHERE     (dbo.employees.status = 'active') AND (dbo.usertraining.completed = 'yes') AND (dbo.usertraining.courseID = '39')

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

try the below:
SELECT  a.ID, 
	c.id AS tc.id, 
	a.last_name, 
	a.first_name, 
	a.dept, 
	a.status, 
        c.course_name, 
	b.empID, 
	b.courseID, 
	b.completed
FROM (select * from dbo.employees where status = 'active') a
LEFT JOIN (select * from dbo.usertraining where completed = 'yes' and courseID = '39') b ON a.ID = b.empID 
INNER JOIN dbo.courses c ON b.CourseID = c.id
WHERE b.empid is null

Open in new window

or
SELECT  a.ID, 
	b.tcid, 
	a.last_name, 
	a.first_name, 
	a.dept, 
	a.status, 
        b.course_name, 
	b.empID, 
	b.courseID, 
	b.completed
FROM (select * from dbo.employees where status = 'active') a
LEFT JOIN (
	select t1.*, t2.id as tcid, t2.course_name
	from dbo.usertraining t1 where t1.completed = 'yes' and t1.courseID = '39'
	INNER JOIN dbo.courses t2 ON t1.CourseID = t2.id	

	) b ON a.ID = b.empID 
WHERE b.empid is null

Open in new window

Author

Commented:

SELECT  a.ID,
      b.tcid,
      a.last_name,
      a.first_name,
      a.dept,
      a.status,
        b.course_name,
      b.empID,
      b.courseID,
      b.completed
FROM (select * from dbo.employees where status = 'active') a
LEFT JOIN (
      select t1.*, t2.id as tcid, t2.course_name
      from dbo.usertraining t1 where t1.completed = 'yes' and t1.courseID = '39'
      INNER JOIN dbo.courses t2 ON t1.CourseID = t2.id      

      ) b ON a.ID = b.empID
WHERE b.empid is null

This gives me incorrect syntax near keyword 'INNER',
the other one had an error to do with putting brackets around

c.id AS [tc.id]
but didn't produce any results, but I know from a manual count that isn't correct.
why do you have all the column names preceeded by dbo?

Here's the query...  this will show all employees who have not completed a course you specify.

Be sure to change the course ID in the query below from 1234 to a valid course ID...



SELECT   dbo.employees.ID
       , dbo.courses.id AS tc.id
       , dbo.employees.last_name
       , dbo.employees.first_name
       , dbo.employees.dept
       , dbo.employees.status 
FROM  dbo.employees 
WHERE dbo.employees.status = 'active'
AND NOT EXISTS (select 1
   from dbo.usertraining
   where dbo.employees.ID = dbo.usertraining.empID
   and   dbo.completed = 'yes'
   and   dbo.courseID = 1234
   )

Open in new window

like this
SELECT  a.ID, 
      b.tcid, 
      a.last_name, 
      a.first_name, 
      a.dept, 
      a.status, 
        b.course_name, 
      b.empID, 
      b.courseID, 
      b.completed
FROM (select * from dbo.employees where status = 'active') a
LEFT JOIN (
      select t1.*, t2.id as tcid, t2.course_name
      from dbo.usertraining t1 
      INNER JOIN dbo.courses t2 ON t1.CourseID = t2.id      
      where t1.completed = 'yes' and t1.courseID = '39'
      ) b ON a.ID = b.empID 
WHERE b.empid is null

Open in new window

ralmada,

in your where clause you are telling your SELECT to return only records where b.empID is NULL
but on your join, you are saying to only match records where b.empID matches, so no records will be returned

Also, since no records will return from the subquery, there is no point in displaying fields in the select from alias "b."  .. .they will always be empty, right?


If you want to use the left join method, something like this could work...
SELECT a.ID, 
       a.last_name, 
       a.first_name, 
       a.dept, 
       a.status
from employees a
  left join usertraining t1 on t1.empID = a.emptID
                           and t1.courseID = 39
                           and t1.completed = 'Yes'
where a.status = 'active'
and   t1.tcid is null

Open in new window

Looks like I used the wrong column name for the employee ID in the join.

@rattie tip:   you should name your columns the same in every time. For example, the employee ID should be "employeeID" in every table, even the employee table.  It adds confusion to have many columns called "ID" and then try to match them with a different named column in another table.  

SELECT a.ID, 
       a.last_name, 
       a.first_name, 
       a.dept, 
       a.status
from employees a
  left join usertraining t1 on t1.empID = a.ID
                           and t1.courseID = 39
                           and t1.completed = 'Yes'
where a.status = 'active'
and   t1.tcid is null

Open in new window

>>in your where clause you are telling your SELECT to return only records where b.empID is NULL
but on your join, you are saying to only match records where b.empID matches, so no records will be returned <<

Wrong, because I'm using left join.

 >>Also, since no records will return from the subquery, there is no point in displaying fields in the select from alias "b."  .. .they will always be empty, right<<

True, but the query can be easily reused to display all employees, those who has and those who hasn't completed any training, just by removing the where clause there. Check the code attached below

>>If you want to use the left join method, something like this could work...<<

Just fyi, I know how to use the left join method, what you're showing is just another alternative, not the only one...
SELECT  a.ID, 
      b.tcid, 
      a.last_name, 
      a.first_name, 
      a.dept, 
      a.status, 
        b.course_name, 
      b.empID, 
      b.courseID, 
      b.completed
FROM (select * from dbo.employees where status = 'active') a
LEFT JOIN (
      select t1.*, t2.id as tcid, t2.course_name
      from dbo.usertraining t1 
      INNER JOIN dbo.courses t2 ON t1.CourseID = t2.id      
      where t1.completed = 'yes' and t1.courseID = '39'
      ) b ON a.ID = b.empID

Open in new window

Author

Commented:
Ralmada,

That last query you posted seems to be working the best, how do I change it to only get those who haven't completed the training?

> you are saying to only match records where b.empID matches, so no records will be returned

> Wrong, because I'm using left join.

Oops, I got distracted.  I meant to finish by saying.  so no records will be returned by the sub query.  Therefore there is no point in selecting so many fields in the subquery.



You're right, it is just another way.   I would think selecting two full subqueries and then joining them together would not be nearly as efficient as the Left Join that you also know how to do...   why don't you show the left join example as well, that would be simplier and more efficient..



> That last query you posted seems to be working the best, how do I change it to only get those who haven't completed the training?

How can it be working best, if it doesn't return only those that haven't been completed - Lol


What happened when you the two queries I provided?



>>That last query you posted seems to be working the best, how do I change it to only get those who haven't completed the training?<<

like in comment http:#a35777402

Author

Commented:
This worked perfectly (with the additional WHERE clause from the earlier query posted).

> This worked perfectly (with the additional WHERE clause from the earlier query posted).


You should at least accept the post that actually answers the question.   Future EE readers would like to see the accepted answer as the resolution, not have to read the entire post to find the right answer.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial