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