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 -> Help with join

Help with join

From: gene <gene01_at_smalltime.com>
Date: 2000/05/16
Message-ID: <gene01-6C12AB.22502515052000@news.concentric.net>#1/1

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.

-- 
Received on Tue May 16 2000 - 00:00:00 CDT

Original text of this message

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