Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Help with join
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