Help with a query

From: Daniel Looby <iasysdl_at_prism.gatech.EDU>
Date: 18 Aug 93 17:40:46 GMT
Message-ID: <109317_at_hydra.gatech.EDU>


I need to return in a query a single row for each person queried and found in table A regardless of whether they have a corresponding record in tables B and/or C (i.e.: outer join). If they have one or more records in table B and/or C then I need the most recent entry (format DD-MON-YYYY). If more than one entry has the most recent entry date then select any one (i.e.: MAX).

Table A  Person_ID .....	Table D  Process_ID .....
         --------- -----                 ---------- -----	
         17400				 15
         57018				 21
         57200				 33
         57217				 44
					 50
					 56

Table B  Person_ID Process_ID Entry_Date
         --------- ---------- -----------
         57200     21	      10-MAY-1992
         57200     33         10-MAY-1992
         57018     44         10-MAY-1993

Table C  Person_ID Process_ID Entry_Date
         --------- ---------- -----------
         57217     56         03-JAN-1993
         57217     44         01-APR-1993
         57018     15         01-FEB-1992
         57018     50         10-MAY-1993

So a query of Person_ID 57018 should return:

    Person_ID Process_ID

  • ---------- 57018 50

A query of Person_ID 57200 should return:  

    Person_ID Process_ID

  • ---------- 57200 33

A query of Person_ID 57217 should return:  

    Person_ID Process_ID

  • ---------- 57217 44

And a query of Person_ID 17400 should return:

    Person_ID Process_ID

  • ---------- 17400

A query of a non-existent Person_ID should return the 'no rows selected'.

Help! (and Thanks.)

-- 
Daniel Looby                    Members of the Bathroom Reader's Institute sit 
631 Cherry Street NW              firmly for what they believe in!
Georgia Institute of Technology   
Atlanta Georgia, 30332          Phone 404-894-9587  
Received on Wed Aug 18 1993 - 19:40:46 CEST

Original text of this message