Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Help with join
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
![]() |
![]() |