Re: Outer join query

From: Doug O'Leary <dkoleary_at_mediaone.net>
Date: 2000/02/15
Message-ID: <MPG.1313c16a73ebd84798973c@nntp.ce.mediaone.net>#1/1

[This followup was posted to comp.databases.oracle.misc and a copy was sent to the cited author.]

Hi;

I got it. At least it works on my test tables.

Here's the crtest.sql to set things up - shows what I'm basing the answer on:

---

drop table objects cascade constraints;

create table objects
( object_id number(4),
object_name varchar2(10),
constraint obj_id_pk primary key(object_id));

drop table services cascade constraints;

create table services

(	object_id number(4),
	s_date date,
	constraint svc_oid_fk foreign key (object_id)
		references objects(object_id));

alter session set nls_date_format = 'MM/DD/YYYY';

insert into objects values(1,'aaa');
insert into objects values(2,'bbb');
insert into objects values(3,'ccc');
insert into objects values(4,'ddd');

insert into services values(2,'1/1/2000');
insert into services values(2,'12,30/1999');

insert into services values(3,'7/4/2000'); insert into services values(3,'5/2/1999');

---

And here's the sql:

---

select a.object_id, a.object_name, max(b.s_date) from objects a, services b
where a.object_id = b.object_id(+)
group by a.object_id, a.object_name;

---

And, it results in:

---

OBJECT_ID OBJECT_NAM MAX(B.S_DA
--------- ---------- ----------

        1 aaa
        2 bbb        01/01/2000
        3 ccc        07/04/2000
        4 ddd


==================

You'll probably want to pretty that up a bit, but if I understood your question right, that should be it.

Doug

-- 


==============

Douglas K. O'Leary
Senior System Admin
dkoleary_at_mediaone.net


==============

Received on Tue Feb 15 2000 - 00:00:00 CST