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')
ASKER
WHERE (dbo.employees.status = 'active') AND (dbo.usertraining.completed = 'yes') AND (dbo.usertraining.courseID = '39')
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 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
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
but didn't produce any results, but I know from a manual count that isn't correct.
c.id AS [tc.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 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 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 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
ASKER
ASKER
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.
TRUSTED BY
dbo.courses.course_name, dbo.usertraining.empID, dbo.usertraining.courseID,
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.complete
AND dbo.usertraining.empID IS NULL