CODEWITHSUNDEEP
sqloracle-database
hdy
hdy

Reputation: 1

Matching data in two different tables (compare data)

I have problem while matching data from 2 different tables. example 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(+)

sorry for my bad english...

Edit:

sorry i cant type well using smartphone.. maybe like this..

table1

col a--b--c--d--e
1st_row 'Ryan'--'Sofia'--'Bulgaria'--'January'--'107'
2nd_row 'Dony'--'Vienna'--'Austria'--'March'--'103'
3rd_row 'Ryan'--'Berlin'--'Germany'--'January'--'107'
4th_row 'Dony'--'Milan'--'Italy'--'March'--'103'

table2

col a--d--e--f--g
1st_row 'Ryan'--'January'--'107'--'Travel'--'5'
2nd_row 'Ryan'--'January'--'107'--'Bussiness'--'4'
3rd_row 'Dony'--'March'--'103'--'Bussiness'--'9'
4th_row 'Dony'--'March'--'103'--'Bussiness'--'3'

in my 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 my wanted result 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

Upvotes: 0

Views: 357

Answers (2)

hdy
hdy

Reputation: 1


sorry i cant type well using smartphone..
maybe like this..

table1
col a--b--c--d--e
1st_row 'Ryan'--'Sofia'--'Bulgaria'--'January'--'107'
2nd_row 'Dony'--'Vienna'--'Austria'--'March'--'103'
3rd_row
'Ryan'--'Berlin'--'Germany'--'January'--'107' 4th_row 'Dony'--'Milan'--'Italy'--'March'--'103'

table2
col a--d--e--f--g
1st_row 'Ryan'--'January'--'107'--'Travel'--'5'
2nd_row 'Ryan'--'January'--'107'--'Bussiness'--'4'
3rd_row 'Dony'--'March'--'103'--'Bussiness'--'9'
4th_row 'Dony'--'March'--'103'--'Bussiness'--'3'

in my 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 my wanted result 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

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141


You can use below query

select distinct x.a, y.a, x.d, y.d, x.e, y.e 
from table1 x
inner join table2 y
on (x.a = y.a(+) and x.d = y.d(+) and x.e = y.e(+));

Upvotes: 0

Related Questions