I have 2 tables. Person is the master table with primark key catcode, slnumb. travel is the detail table with primary key catcode, slnumb, depdate. some slnumb may not have corresponding records in the travel table.

I need a query which will return catcode, slnumb, name, location and if the location is null then MAX(depdate) and the corresponding arrdate from the (travel table) same row

desc person CATCODE NOT NULL VARCHAR2(3) SLNUMB NOT NULL NUMBER(6) NAME NOT NULL VARCHAR2(35) LOCATION VARCHAR2(3)

desc travel CATCODE NOT NULL VARCHAR2(3) SLNUMB NOT NULL NUMBER(6) DEPDATE NOT NULL DATE ARRDATE DATE

-- Sreenivas Kolanthayappan (SREENIK@OMANTEL.NET.OM), December 04, 2003