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

Home -> Community -> Usenet -> c.d.o.tools -> Re: Help with join

Re: Help with join

From: <www_dba_at_my-deja.com>
Date: 2000/05/16
Message-ID: <8fqs7r$a4h$1@nnrp1.deja.com>#1/1

Is this OK?

 select e.name, e.boss, l1.CITY loc, l2.CITY boss_loc  from

       emp       e,
	   boss      b,
	   location l1,
	   location l2
 where
       e.loc = l1.loc and
	   b.loc = l2.loc(+) and
	   e.boss = b.name(+);

In article <gene01-6C12AB.22502515052000_at_news.concentric.net>,   gene <gene01_at_smalltime.com> wrote:
> I'm attempting to do join several tables to create a view
> but have run into a problem. Here is a simplified view
> of the data:
> Employees have a name, a location, and zero or one boss.
> Bosses have a name, a location, and zero, one, or multiple employees.
>
> EMP:
> Name Boss Loc
> ---- ----- ----
> Jane Amy 1
> Juliette 2
> Janice Anne 2
>
> BOSS:
> Name Loc
> ----- ----
> Amy 3
> Anne 2
> Alice 1
>
> LOCATION:
> Loc City
> ---- -----
> 1 New York
> 2 Paris
> 3 Moscow
>
> I want to create this view:
> emp name boss name emp loc boss loc
> --------- --------- ------- --------
> Jane Amy New York Moscow
> Juliette Paris
> Janice Anne Paris Paris
>
> What I tried was:
> select *
> from
> emp, boss,
> location l1,
> location l2
> where
> emp.loc = l1.loc and
> emp.boss = boss.name(+) and
> boss.loc = l2.loc
>
> Unfortunately, that last join is the killer. If
> emp.boss is null, then boss.loc is null and
> boss.loc = l2.loc doesn't match anything. The
> best I could come up with was a kludge using a
> default location like this:
>
> select *
> from
> emp, boss,
> location l1,
> location l2
> where
> emp.loc = l1.loc and
> emp.boss = boss.name(+) and
> l2.loc = nvl(boss.loc, 1)
>
> A little help would be greatly appreciated.
>
> --
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 16 2000 - 00:00:00 CDT

Original text of this message

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