Toad World® Forums

Joining/Merge two keys to one key and removing duplicates

I want to merge two tables using SQL (Toad for Oracle) merging one key from one table to two keys of the main table while deleting the duplicated entries. I guess an illustration provides you better understanding of my problem:

Dataframe A:

    Key1 Key2 ColA 
    1     2    1993
    1     2    1992
    1     4    1991
    2     4    1990
    2     5    1989 
    2     5    1988
    3     5    1987
    3     6    1986
    3     6    1985

Dataframe B:

Key1&2  ColB
1       Adress1
1       Adress1
1       Adress1
2       Adress2
2       Adress2
3       Adress3
3       Adress3
3       Adress3
4       Adress4
4       Adress4
5       Adress5
6       Adress6
6       Adress6

Desired Dataframe:

Key1 Key2 ColA  ColB-1     ColB-2
1     2    1993 Adress1   Adress2
1     2    1992 Adress1   Adress2 
1     4    1991 Adress1   Adress4
2     4    1990 Adress2   Adress4
2     5    1989 Adress2   Adress5
2     5    1988 Adress2   Adress5
3     5    1987 Adress3   Adress5
3     6    1986 Adress3   Adress6
3     6    1985 Adress3   Adress6

I tried using following statement so far:

SELECT *
FROM A
LEFT JOIN B ON A.key1=B.key1&2
LEFT JOIN B ON A.key2=B.key1&2

However, as I explained, because dataframe B has duplicated rows in its keys, duplicated rows in my output as well.
Hope its clear.

Thanks,

KS