0

Tables Details:

CREATE TABLE Test2 ( ID INT, Value INT, other INT);

CREATE TABLE Test1 ( ID INT, TYPE INT, other INT);

INSERT INTO Test2 VALUES (123456, 5, 12); INSERT INTO Test2 VALUES (123456, 10, 17);

INSERT INTO Test1 VALUES (123456, 00, 2); INSERT INTO Test1 VALUES (123456, 01, 6); INSERT INTO Test1 VALUES (123456, 02, 4);

INSERT INTO Test1 VALUES (987654, 00, 7); INSERT INTO Test1 VALUES (987654, 01, 8);

INSERT INTO Test1 VALUES (456789, 00, 6); INSERT INTO Test1 VALUES (456789, 01, 16);

This is the Query i m using to avoid duplicate from table Test

SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

Query Result:

ID TYPE other value 123456 0 2 5 123456 0 2 10 123456 1 6 5 123456 1 6 10 123456 2 4 5 123456 2 4 10

Description:

Expected Result is , when i m fetching match records from tables. Should get the records from left table(Test1) without duplicate records and avoid duplicate record from right table (Test2).

Reference Check Pic

Expected Query Result are,

23456 0 2 5 123456 1 6 10 123456 2 4

SQL Fiddle Link

http://sqlfiddle.com/#!9/3cd8a0/26

Criteria When there is ID match, should get matched records from left table (Test1) without duplicate and from right table (Test2) without duplicate. enter image description here

0

Although you can JOIN the 2 tables ON their IDs, you'll probably need to introduce "artificial" rownumbers (as it were), and use these in your join, too.

TABLES and data

select * from test1 ;
+------+----+-----+
|ID    |TYPE|OTHER|
+------+----+-----+
|123456|0   |2    |
|123456|1   |6    |
|123456|2   |4    |
|987654|0   |7    |
|987654|1   |8    |
|456789|0   |6    |
|456789|1   |16   |
+------+----+-----+


select * from test2 ;
+------+-----+-----+
|ID    |VALUE|OTHER|
+------+-----+-----+
|123456|5    |12   |
|123456|10   |17   |
+------+-----+-----+

SELECT and ROW_NUMBER()

  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1 ;

+------+----+-----+---+
|ID    |TYPE|OTHER|RN_|
+------+----+-----+---+
|123456|0   |2    |1  |
|123456|1   |6    |2  |
|123456|2   |4    |3  |
|456789|0   |6    |1  |
|456789|1   |16   |2  |
|987654|0   |7    |1  |
|987654|1   |8    |2  |
+------+----+-----+---+

  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2 ;

+------+-----+-----+---+
|ID    |VALUE|OTHER|RN_|
+------+-----+-----+---+
|123456|5    |12   |1  |
|123456|10   |17   |2  |
+------+-----+-----+---+

If you now JOIN these 2 result sets, you may be a step closer to the solution that you are after.

select T1.id, T1.type, T1.other, T2.value
from (
  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1
) T1 left join (
  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2
) T2 on T1.id = T2.id and T1.rn_ = T2.rn_ ;

+------+----+-----+-----+
|ID    |TYPE|OTHER|VALUE|
+------+----+-----+-----+
|123456|0   |2    |5    |
|123456|1   |6    |10   |
|456789|0   |6    |NULL |
|456789|1   |16   |NULL |
|987654|0   |7    |NULL |
|987654|1   |8    |NULL |
|123456|2   |4    |NULL |
+------+----+-----+-----+

Now you just need to eliminate the rows whose IDs are not in Table2.

-- final query
select T1.id, T1.type, T1.other, T2.value
from (
  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1
) T1 left join (
  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2
) T2 on T1.id = T2.id and T1.rn_ = T2.rn_
where exists ( select null from test2 where id = T1.id )
order by 1, 2 ;

+------+----+-----+-----+
|ID    |TYPE|OTHER|VALUE|
+------+----+-----+-----+
|123456|0   |2    |5    |
|123456|1   |6    |10   |
|123456|2   |4    |NULL |
+------+----+-----+-----+

DBfiddle (Oracle 18c) SQLfiddle (Oracle 11g)

| improve this answer | |
  • Thanks a lot............... Its working perfectly – Kathir E Jun 23 at 16:25
  • You are welcome :-) It seems to be a lot of code for the final query - which may be easier to understand if one can see the "intermediate" steps, too. – stefan Jun 23 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.