Re: Access Right/Left Joins to Oracle (+) joins - Theorectical Question

From: JonWat <jonwaterhouse_at_gov.nl.ca>
Date: 23 Oct 2006 12:31:59 -0700
Message-ID: <1161631919.529835.44750@m73g2000cwd.googlegroups.com>

mgillesp_at_uoguelph.ca wrote:
> Yeah I read that in a few online articles and such! Here's the
> problem.... Im on Oracle 8i.
>
> And because of certain political reason (or something????), we cannot
> upgrade at this time. Even though Ive suggested it on many occasions.
> I'd have to say being a coop student doesn't have a lot of pull in
> government positions :P
>
> Any other input??
>
> Thanks,
>
> Mitch
>
>
>
>
> >
> > You can write it as is in 9i and above (ANSI join syntax is supported).

Based on your other post, looks like you are mostly just looking up values in lookup tables.
Another way of structuring your query is the bottom version. Easier to understand in my opinion. Will give you nulls where the main file is missing a "code".

Jon

create table test_main

(id number,
 l1 number,
 l2 number,

 l3 number);

 create table lookup1
 (id number,
  descrip varchar2(10));

  create table lookup2
 (id number,
  descrip varchar2(10));

  create table lookup3
 (id number,
  descrip varchar2(10));

  insert into test_main
  values (1,1,1,1);

  insert into test_main
  values (2,2,1,2);

  insert into test_main
  values (6,2,2,1);

    insert into test_main
  values (3,NULL,2,1);

   insert into test_main
  values (4,NULL,1,NULL);

     insert into test_main
  values (5,NULL,NULL,2);

  insert into lookup1
  values (1,'male');

  insert into lookup1
  values (2,'female');

insert into lookup2
  values (1,'dead');

 insert into lookup2

    values (2,'alive');

select t.id,l1.descrip,l2.descrip
from test_main t,
lookup1 l1,
lookup2 l2
where t.L1=l1.ID (+)
and t.L2=l2.ID (+)

select t.id,

       (select l1.descrip from lookup1 l1
	    where l1.id=t.l1) d1,
		(select l2.descrip from lookup2 l2
	    where l2.id=t.l2) d2

from test_main t Received on Mon Oct 23 2006 - 14:31:59 CDT