Re: weird type of outer join... matching only 1 row ??

• Post for FREE via your newsreader at post.usenet.com ****

Hi Rene

> create table table1 (
> key number (1),
> value1 number (4)
> );
>
> create table table2 (
> key number (1),
> field char (1),
> value2 number (4)
> );
>
> insert into table1 values (1, 1000);
> insert into table1 values (2, 1000);
>
> insert into table2 values (1, 'A', 200);
> insert into table2 values (1, 'B', 300);
> insert into table2 values (1, 'C', 50);
> insert into table2 values (3, 'A', 60);
>
> select distinct * from (
> select
> t1.key, t2.field, t1.value1, t2.value2
> from
> table1 t1 left join table2 t2 on t1.key = t2.key
> union
> select
> t1.key, t2.field, t1.value1, t2.value2
> from
> table1 t1 right join table2 t2 on t1.key = t2.key);
>
> drop table table1;
> drop table table2;

If I run your code I get the following output:

       KEY F VALUE1 VALUE2
---------- - ---------- ----------

         1 A       1000        200
         1 B       1000        300
         1 C       1000         50
         2         1000
           A                    60

i.e. this is not what Cip is looking for...

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=