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

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 17 Jul 2004 12:52:27 GMT
Message-ID: <slrncfi887.3mo.rene.nyffenegger@zhnt60m34.netarchitects.com>

> In article <9f0f419b.0407160130.20ae9828_at_posting.google.com>, Cip wrote:

>> Hi I'm having some trouble with a challenging type of join I dont even
>> know if it has a name:
>>
>> Lets say I have a one-to-many relationship between Table1 and Table2.
>>
>> Table1 has 2 fields (Key, Value1) ("Key" is unique)
>> and Table2 has 3 fields (Key, Field, Value2) ("Key" is not unique)
>>
>> In the end I want a result table with: Key, Field, Value1 and Value2.
>>
>> However I want each row from Table1 matched to Table2 at most 1 times.
>> IE if table2 has many rows with key='123' I want the joined result
>> table to have only 1 match from Table1, the rest should be null.
>>
>> This is hard to explain...
>>
>>
>> Table1:
>> Key Value1
>> --- -----
>> 1 1000
>> 2 1000
>>
>> Table2:
>> Key Field Value2
>> --- ----- ------
>> 1 A 200
>> 1 B 300
>> 1 C 50
>> 3 A 60
>>
>> If I do my type of "Join" on these two tables I want this result:
>>
>> Key Field Value1 Value2
>> --- ----- ------ ------
>> 1 A 1000 200
>> 1 B NULL 300 <----- I, Rene, believe
>> 1 C NULL 50 <----- the NULLs for
>> 2 NULL 1000 NULL Value1 are wrong
>> 3 A NULL 60
>>
>> So its a pseudo FULL OUTER JOIN with each Value field appearing only
>> once to each match, the rest are null.
>>
>> I dont even know if this is possible as how can I tell Oracle that if
>> there are 2 matches for Key=1 I only wanna match the "first" one... (I
>> know there is no such thing as a "first" one...)
>>
>> Dont know if this matters, but in Table2, Key+Field form a unique key
>> for that table.
>>
>> If ANYONE can help me it would be much appreciated.
 

Sorry, for my prior posting, something went wrong.

It looks like you're after something like this:  

   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;

hth

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Sat Jul 17 2004 - 07:52:27 CDT