SQL (Subquery ) question 1

[tunjio]

II have got two tables – employees and payroll that have an outer join between them because not all employees have a value in the payroll table.

In addition the payroll table has an effective date value and I need to find out the latest effective date.

For instance I have a PS_PERSONAL_DATA table as below

EMPLID
1000621
1000625
6018137

and a PS_PI_EMPLID_TBL

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
1000625 PDP 01/01/2005

I want a query to display the following

EMPLID PI_SYSTEM_ID EFFDT
1000621 ADP 01/03/2005
1000625 PDP 01/01/2005
6018137

Any ideas please!!!

 

[kskid]

Try this

Select
  PS_PERSONAL_DATA.EMPLID,
  PS_PI_EMPLID_TBL.PI_SYSTEM_ID,
  PS_PI_EMPLID_TBL.EFFDT
from
  PS_PERSONAL_DATA,
  PS_PI_EMPLID_TBL
WHERE
  (
   PS_PI_EMPLID_TBL.EFFDT IS NULL OR
   PS_PI_EMPLID_TBL.EFFDT = 
  (SELECT
     MAX(PI.EFFDT)
   FROM
     PS_PI_EMPLID_TBL PI
   WHERE
     PI.EMPLID = PS_PERSONAL_DATA.EMPLID)
   )
AND
  PS_PI_EMPLID_TBL.EMPLID(+) = PS_PERSONAL_DATA.EMPLID

-LW

 

[tunjio]

Thanks very much for this insight . It worked like a treatand you've made someone very happy