Join Problem??

[dukeslater]

I'm having trouble with what surely is a join problem, but I've tried all the usual suspects with no success. If I can accomplish the same thing with a completely different technique please feel free to point that out.

I need to return the employee ID, the date of their first paycheck, the date of their most recent paycheck, and the date of their second most recent paycheck.

Restricting the query to a small subset (OU_ID is a company ID), I know I should return 190 records. However, the following statement only returns 161 records (I've deleted all outer join syntax). The missing records represent employees who only received one paycheck, therefore they had no second most recent paycheck. Rather than returning a null, the query is dropping those records.

*************
select a.emp_id, min(a.d_pr_date), max(a.d_pr_date),
max(b.d_pr_date)

from tc_base a,tc_base b

where a.emp_id = b.emp_id

and b.d_pr_date < a.d_pr_date
and a.ou_id = 211
and b.ou_id = 211

group by a.emp_id

*************

Thanks in advance for your help. I have no doubt that it will be a simple answer that has escaped me.

 

[carp]

OK - this looks good so far. It should work once you get the outer join fixed.

 

[tlbroadbent]

SELECT
a.emp_id,
min(a.d_pr_date),
max(a.d_pr_date),
max(b.d_pr_date)

FROM tc_base a LEFT JOIN tc_base b
ON a.emp_id = b.emp_id

WHERE a.ou_id = 211
AND (b.d_pr_date < a.d_pr_date OR b.d_pr_date IS NULL)
AND (b.ou_id = 211 OR b.ou_id IS NULL)

GROUP BY a.emp_id

Depending on your RDMS, max(b.d_pr_date) may not work properly because some aggregate functions ignore NULLS. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous