Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: outer join with (select max(...)) into it.

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

From: Richard Spee <rhpspee_at_wxs.nl>
Date: Fri, 7 Jun 2002 13:05:35 +0200
Message-ID: <adq4fb$2ia$1@reader09.wxs.nl>


Jochen,

Oracle does not allow outer join to a subquery (ORA--01799) Try the following script.

create table master (id number);
create table detail (master_id number, date_created date);

insert into master values (1);
insert into master values (2);
insert into master values (3);
insert into detail values (1,sysdate);
insert into detail values (1,sysdate+1);
insert into detail values (3,sysdate);

select id, master_id,date_created
from master
, (select master_id, max(date_created) as date_created from detail group by master_id) detail where master.id=detail.master_id(+)
/

"Jochen" <Jochen_nospam_at_pophost.eunet.be> wrote in message 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)(+)
>
> But ofcourse the last (+) doesn't work... any ideas/workarounds?
>
> TIA,
>
> Jochen.
Received on Fri Jun 07 2002 - 06:05:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US