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

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. Received on Fri Jul 16 2004 - 04:30:10 CDT