SQL: outer join with (select max(...)) into it.

Hello,

Could you help me solve this problem:
I'm trying to outer join 2 tables, but I want an extra condition with a subclause in it and I don't know how to outer join that one.

(simplified example)
master table: id, name.
detail table: id, id_master, eventdate, eventname.

What i want to do is to select all the master records and with every master record one record from the detail table, but the one with the highest date. If there's no detail record, I still want the master record (so outer join).

So I wanted:
select m.id, m.name, d.eventname, d.eventdate from master m, detail d
where m.id = d.id_master(+)
and d.eventdate = (select max(eventdate) from detail where id = d.id)(+)

But ofcourse the last (+) doesn't work... any ideas/workarounds?

TIA, Jochen. Received on Fri Jun 07 2002 - 04:51:49 CDT