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
> 1 C NULL 50
> 2 NULL 1000 NULL
> 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.
--
Rene Nyffenegger
http://www.adp-gmbh.ch/
Received on Sat Jul 17 2004 - 07:40:06 CDT
