SELECT distinct tbl_emp.lastname, tbl_emp.firstname, CASE active WHEN 1 then 'Yes'WHEN 0 then 'No' else 'other'END
FROM (dept_table INNER JOIN ((contributions INNER JOIN charity_table ON contributions.charity_code = charity_table.charity_code) 
                 INNER JOIN tbl_emp ON contributions.employee_ID = tbl_emp.employee_id) ON dept_table.dept_code = tbl_emp.dept_code) 
WHERE Year(contributions.date_stamp)=Year(getDate())
GROUP BY tbl_emp.lastname, tbl_emp.firstname, tbl_emp.employee_id, dept_table.dept_name, charity_table.ded_code,active;

SELECT distinct tbl_emp.lastname, tbl_emp.firstname, 
	CASE active WHEN 1 then 'Yes'WHEN 0 then 'No' else 'other'END
FROM contributions c
INNER JOIN charity_table ct ON c.charity_code = ct.charity_code
LEFT JOIN tbl_emp e ON e.employee_id = c.employee_id
INNER JOIN dept_table d ON d.dept_code = e.dept_code
WHERE Year(c.date_stamp)=Year(getDate())
GROUP BY e.lastname, e.firstname, e.employee_id, d.dept_name, ct.ded_code, active;

quote:

---

Originally posted by simflex

Thank you Bandi,

That's exactly how I did the LEFT JOIN earlier.

This time, however, I decided to remove the WHERE clause:

WHERE Year(c.date_stamp)=Year(getDate()) and it worked.

Also, when I used the WHERE clause for a previous year

WHERE Year(c.date_stamp)='2011', it still worked.

The question is why isn't it working when date_stamp = current year?

After all, the idea of LEFT JOIN is to show what matter between 2 tables, and everything else that didn't match on the left table, no?

In this case, show what matched between various joins and everything else on the tbl_emp table that didn't match.

I am really baffled.

---