Need help with a complex SQL query

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.

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

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

Select allOpen 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

Select allOpen in new window

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
   )

Select allOpen 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

Select allOpen 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

Select allOpen 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

Select allOpen in new window

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

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.