CODEWITHSUNDEEP
sqloracle-databaseselect
Navin
Navin

Reputation: 299

select query on two tables with no unique keys

I have two tables.

Table1


col1   col2  col3

100    A     1000 
100    A     1000
100    A     1002
100    B     1003
100    C     1004

Table2


col1   col2  colC

100    A     1X 
100    A     2X  
100    A     3X
100    B     4X
100    C     5X

In the above table colC value is unique.

i want my ouptput to be like this, where colC values to be unique here also.


col1   col2  col3  colC

100    A     1000  1X 
100    A     1000  2X  
100    A     1002  3X
100    B     1003  4X
100    C     1004  5X

I have to use col1 and col2 as the key for the join.

Is that possible to do that. i got duplicates comming in for the first two records, when i tried with inner and left outer joins. TIA

Upvotes: 0

Views: 526

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40543

Something like this?

select
  a.col1,
  a.col2,
  a.col3,
  b.colC 
from (
  select
    row_number() over (partition by col1, col2 order by 1) r,
    col1,
    col2
  from 
   table1
  ) a,  (
  select
    row_number() over (partition by col1, col2 order by 1) r,
    col1,
    col2
  from 
   table2
  ) b
  where a.r = b.r and
        a.col1 = b.col1 and
        a.col2 = b.col2;

Upvotes: 3

anothershrubery
anothershrubery

Reputation: 21023

SELECT t1.col1, t1.col2, t1.col3, t2.colC FROM Table1 t1
JOIN Table2 t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2

Is this not right?

EDIT: You say you get duplicates, but this will happen as the 2 columns you specify as keys, are not actually keys. There are other rows with the same values. So if the combination is meant to be unique there is either a fault with the data or your requirements need to be looked into again.

Upvotes: 2

Related Questions