Avatar of rrattie
rrattieFlag for United States of America

asked on 

Need help with a complex SQL query

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


Microsoft SQL Server 2005ColdFusion LanguageSQL

Avatar of undefined
Last Comment
gdemaria
Avatar of knightEknight
knightEknight
Flag of United States of America image

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
Avatar of knightEknight
knightEknight
Flag of United States of America image

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.
Avatar of rrattie
rrattie
Flag of United States of America image

ASKER

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

Avatar of ralmada
ralmada
Flag of Canada image

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

Avatar of ralmada
ralmada
Flag of Canada image

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

Avatar of rrattie
rrattie
Flag of United States of America image

ASKER


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.
Avatar of gdemaria
gdemaria
Flag of United States of America image

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

Avatar of ralmada
ralmada
Flag of Canada image

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

Avatar of gdemaria
gdemaria
Flag of United States of America image

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

Avatar of gdemaria
gdemaria
Flag of United States of America image

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

ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rrattie
rrattie
Flag of United States of America image

ASKER

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?

Avatar of gdemaria
gdemaria
Flag of United States of America image

> 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?



Avatar of ralmada
ralmada
Flag of Canada image

>>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
Avatar of rrattie
rrattie
Flag of United States of America image

ASKER

This worked perfectly (with the additional WHERE clause from the earlier query posted).
Avatar of gdemaria
gdemaria
Flag of United States of America image


> 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.


Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo