CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Chidi Okeh
Chidi Okeh

Reputation: 1557

Table Joins is not returning accurate results; any ideas why?

Each year, our department solicits contributions from employees to help support research on things like Cancer Research, drug and acohol, etc.

We have put together a system to help make contributions less stressfull for employees.

Currently, we have tables that are relevant to this issue I am currently having.

They are are emp, dept, and contributions tables.

Emp table contains a list of all employees in our organization.

Dept table contains list of departments.

Finally,each time an employee makes contribution, the employee's ID and date of contributions (date_stamp) are recorded in contributions table.

Each employee is required to complete this form whether s/he is making contributions or not. An employee has the option of contributing, or checking the box that says, "I do not wish to contribute at this time"

I have been tasked with providing a list of employees who have not completed the form.

If an employeeID is on the emp table but not on the contributions table for the current year(date_stamp), then that employee has not completed the form.

My query so far, returns less than half of the employees on the emp table who have not completed the form. We would like this code to return a list of those employees in the emp table who are not yet on the contributions table for the current year.

Right now, since the contribution season begins on 11/27/2012, we would expect this query to return all employees on the emp table.

Your assistance is greatly appreciated.

SELECT  e.lastname, e.firstname,CASE active WHEN 1 then 'Yes'WHEN 0 then 'No' else 'other'END
FROM contributions c
LEFT JOIN emp e ON e.employee_id = c.employee_id
INNER JOIN dept d ON d.dept_code = e.dept_code
WHERE Year(c.date_stamp) <> '2010' OR Year(c.date_Stamp) <> '2011' OR Year(c.date_Stamp) <> '2012'
GROUP BY e.lastname,e.firstname, e.employee_id, active
ORDER By e.lastname;

Upvotes: 3

Views: 80

Answers (2)

Godfre
Godfre

Reputation: 41

Try using EXISTS

select e.lastname, e.firstname,CASE active WHEN 1 then 'Yes'WHEN 0 then 'No' else 'other'END from emp e,dept d where d.dept_code = e.dept_code and not exists( select 'x' from contributions where c.employee_id = e.employee_id)

Hope that helps.

Upvotes: 0

roman
roman

Reputation: 117345

Well if I understand you correctly, you need something like this

select
    e.lastname, e.firstname
from emp as e
where
    e.employee_id not in
    (
         select c.employee_id
         from contributions as c
         where c.date_stamp >= convert(datetime, '20121127', 112)
    )

Upvotes: 5

Related Questions