Help Needed from SQL Gurus

[tunjio]

I have got two tables – employees and payroll that have an outer join between them

A simple query between the two would be as follows:

SELECT PD.EMPLID,PI_SYSTEM_ID,K.EFFDT
FROM INGIPRD.PS_PI_EMPLID_TBL K,INGIPRD.PS_PERSONAL_DATA PD
WHERE PD.EMPLID=K.EMPLID(+)
and PD.EMPLID IN ('1000621','6018137')


Output

EMPLID PI_SYSTEM_ID EFFDT
1000621 ADP 16/08/1994
1000621 ADP 21/08/1996
1000621 ADP 01/02/1998
1000621 ADP 16/04/1998
1000621 ADP 18/05/1998
1000621 ADP 03/10/2002
1000621 ADP 26/03/2003
1000621 ADP 01/03/2005
6018137

As can be seen not all employees have an entry in the Payroll table e.g. EMPLID 6018137. In addition some employees will have multiple rows (1000621)

I need a sub query to retrieve the latest row for an employee and also for those employees without an effective date still would like to have them displayed

So I want a query to display the following


EMPLID PI_SYSTEM_ID EFFDT
1000621 ADP 01/03/2005
6018137

Any ideas please!!!

 

[BrianTyler]

Try

SELECT
PD.EMPLID
,PI_SYSTEM_ID
,max(K.EFFDT)
FROM
INGIPRD.PS_PI_EMPLID_TBL K left outer join
INGIPRD.PS_PERSONAL_DATA PD
on
PD.EMPLID=K.EMPLID
where
PD.EMPLID IN ('1000621','6018137')
group by
PD.EMPLID
,PI_SYSTEM_ID

This is DB2 syntax, you may need to change the expression of the left outer join for your DB.

If this does not work, post your query to the forum for your database.

Brian

 

[tunjio]

Thanks Brian for the post. It dodn't work as expected but I took your advice and posted on the native db forum (oracle 8.1.7)and lo and behold got a working solution.

Good idea - this forum