Match and compare data from two tables

Question

I have problem while matching data from 2 different tables.

table1: a,b,c,d,e (col)
table2: a,d,e,f,g (col)

How to match data in table1 col a,d,e with table2 col a,d,e in condition if row in table1 matched with row in table2 then stop looping?

In my script result always make duplicate while matching data (when data in table1 matched its still looping not locked with the other data in table2).

select distinct x.a, y.a, x.d, y.d, x.e, y.e
from table1 x,
     table2 y
where x.a = y.a(+) and x.d = y.d(+) and x.e = y.e(+)

data sample...

table1

col a--b--c--d--e
'Ryan'--'Sofia'--'Bulgaria'--'January'--'107'
'Dony'--'Vienna'--'Austria'--'March'--'103'
'Ryan'--'Berlin'--'Germany'--'January'--'107'
'Dony'--'Milan'--'Italy'--'March'--'103'

table2

col a--d--e--f--g
'Ryan'--'January'--'107'--'Travel'--'5'
'Ryan'--'January'--'107'--'Bussiness'--'4'
'Dony'--'March'--'103'--'Bussiness'--'9'
'Dony'--'March'--'103'--'Bussiness'--'3'

query

select distinct x.a, y.a, x.d, y.d, x.e, y.e
from table1 x,
     table2 y
where x.a = y.a(+) and x.d = y.d(+) and x.e = y.e(+)

result are
table1 1st_row matched with table2 1st_row
table1 2nd_row matched with table2 3rd_row
table1 3rd_row matched with table2 1st_row (match duplicated)
table1 4th_row matched with table2 3rd_row (match duplicated)

but the result needed are
table1 1st_row matched with table2 1st_row
table1 2nd_row matched with table2 3rd_row
table1 3rd_row matched with table2 2nd_row
table1 4th_row matched with table2 4th_row

i dont understand how to use procedure or case
please help solve this problem... thanks

Please fix your formatting, tell us what database you are using, and also get rid of that archaic join syntax using `(+)`. — Tim Biegeleisen, May 03 '17 at 06:03
sorry tim, im just learning oracle database a week ago, i dont know much what u mean about archaic join syntax.. sorry — Gus Jerinx, May 03 '17 at 06:07
Your current join conditions do not discriminate between rows 1,2 and 3,4 in `table2`. You'll have to add logic to join the way you want. — Tim Biegeleisen, May 03 '17 at 06:15

score 0 · Answer 1 · answered May 03 '17 at 08:47

Number rows in groups and join rows with same number.

select *
  from ( select T1.*, row_number() over(partition by a,d,e order by NULL) num
           from Table1 T1
       ) x
  left join (
         select T2.*, row_number() over(partition by a,d,e order by NULL) num
           from Table2 T2
       ) y
    on x.a = y.a and x.d = y.d and x.e = y.e and x.num=y.num

Match and compare data from two tables

1 Answers1