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

Salut Jochen,

"Jochen" <Jochen_nospam_at_pophost.eunet.be> schrieb im Newsbeitrag news:3D008297.F7CED084_at_pophost.eunet.be...
> 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)(+)
>

try perhaps the following:

Select m.id, m.name, d.eventname, MAX(d.eventdate) FROM master m, detail d
where m.id = d.id_master(+);

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

Matthias Received on Fri Jun 07 2002 - 09:35:18 CDT